Report number of weeks for each month

[irishadar]

Hi,

 

I have a “week – month” problem with following fields:

“Start date”; “End date”; “Weeks”; “Month” and “Year”.

 

For a particular period of time we have e.g.

“Start date”:11/14/2005 and “End date”:01/20/2006 (Notice change of year).

 

I want FileMaker to report number of weeks for each month for this period, in a related table as follows:

“Year”: 2005 ; “Month”: November; “Weeks”: 3

“Year”: 2005 ; “Month”: December; “Weeks”: 4

“Year”: 2006 ; “Month”: January; “Weeks”: 3

 

I’m very thankful for help

[comment]

First, it depends what constitutes a week.

 

Second, to write out ALL the months between “Start date” and “End date", you will either have to impose a limit on the maximum period in between, or use a custom function.

 

Ah, wait - I see you have posted under scripting. OK, a looping script could do this. So only the first question remains.

[irishadar]

The first week:

11/14/2005 (Monday) to 11/20/2005 (Sunday) is week #46; November (This week number I can get from the calculation “Day of week fiscal”)

11/21/2005 (Monday) to 11/27/2005 (Sunday) is week #47; November.

11/28/2005 (Monday) to 12/04/2005 (Sunday) is week #48; November.

12/05/2005 (Monday) to 12/11/2005 (Sunday) is week #48; December.

And so on.

How can I get the summary e.g. for the first month?

“Year”: 2005 ; “Month”: November; “Weeks”: 3

[Maarten Witberg]

Hi

 

I created a sample. I worked from the assumption that you want to know the available time in each month, expressed as weeks (I added the number of days also, say you have 20 days in the first month, that's 2 weeks 6 days. A full month is 4 weeks +0, 2 or 3 days. You can round the result to the nearest full number of weeks.)

 

The sample is fully scripted. I would not know how to do this with a single calculation.

Like Comment suggested, it uses a loop.

The loop is run based on a count of the number of months from the startdate to the enddate. So each

loop step creates a new record, sets month, year and available time in weeks+days.

 

The script makes use of the Date(mm;dd;yyyy) function. Please check if your version uses the same format in the scriptmaker. If it uses Date(dd;mm;yyyy) (as it's formatted in the calc functions in my dutch version.... ) you'll get very wrong results.

The sample records have enddates and startdates in dutch format: dd-mm-yyyy. I don't know how this behaves when opened in an international version that uses mm-dd-yyyy. please check this also.

 

There may be some hidden errors in the script, but the last tests I performed seemed OK. I welcome any comments, corrections and simplifications.

 

It's a V6 sample, you can import into v7 if you have that.

 

 

kjoe

[comment]

I am sorry, my question wasn't clear: I meant, how do you count partial weeks? It's actually two questions:

 

1. How many days are minimum to count a week?

For example, if StartDate is on a Thursday and EndDate is on Tuesday the following week. How many weeks is that?

 

2. How to determine the month to which a week belongs?

For example, if StartDate is Wednesday, Feb 2, 2005. The week begins on Monday, January 31, 2005. Which month is credited for this week?

If you don't mind me saying so, this is mixing apples with oranges. Months are months, and weeks are weeks. Each of these has its own cycle, independent of the other.

 

Perhaps it would be easier to advise, if we knew the purpose of this exercise.

[irishadar]

Kjoe: Many thanks for your solution. It will be useful for me!

 

Comment: Thanks for your questions.

1. Sorry, I haven’t explained that Start date is always on a Monday and End date on a Friday.

2. Concerning month – weeks. The conditions are that the majority of days (Monday to Friday) will decide which month a week belongs to. One example:

Say that 30 and 31:st of January is Monday and Tuesday. 1:st to 3:rd of February is Wednesday to Friday. This week belongs to February, because the majority of days are in this month.

I hope that this explanation makes the problem clearer.

[comment]

OK, that indeed makes it clear. So, IOW, the month to which the week belongs is the month of Wednesday. I am saying this so that you understand what I have done. Hopefully it does what you expect.

[irishadar]

Comment

You have made a brilliant solution for me. Many thanks!/Irishadar