calculating the week number

[stefangs]

i have a calendar file with one record per week which calculates the week number like this:

WeekofYear(monday), where monday is the date of the monday of the week.

as it turns out, the week numbers start at 2 and go through 53. i suppose i could just subtract 1 from that number, but if the first of january happens to fall on a monday, that's probably going to be incorrect then, or is it? is there a more reliable way to do this?

[Vaughan]

Huh?

I just tried 1/1/2003 and the WeekOfYear function returned 1.

[stefangs]

> I just tried 1/1/2003 and the WeekOfYear function returned 1.

sure, but 1/1/2003 is a wednesday. in my calendar file, i have one week per record rather than one day, and so the date on which calcs are based are mondays, in this case 12/30/2002 - the week for that is 53. the next week starts with 1/6/2003 and returns week number 2. perhaps it's incorrect to label the week of 12/30/2002 as '1', but 53 is odd also. but actually, i think this all *only* affects the last/first weeks of the year, i suppose all other weeks are correct, so that's already a big revelation :-)

[BobWeaver]

You can adjust the calculation as you want, but you have to define what a week is and when it begins. In North America the week begins on Sunday and ends Saturday. I believe in Europe the week begins on Monday and ends Saturday. I don't know if the european versions of FileMaker take this into account. Having said that you could try a formula like:

Mod(WeekofYear(Monday)-1, 52)+1 (if weeks begin on Sunday)

or

Mod(WeekofYear(Monday-1)-1, 52)+1 (if weeks begin on Monday)

which will give the week number where week 1 is defined as the week in which January 1 occurs.

[CobaltSky]

I believe the issue here is that the convention that FileMaker follows is that the week runs from Sunday to Saturday, and the first week of the year is the period from a Sunday to a Saturday in which the first of January falls. Thus, if the first of January is a Saturday, then the following day, Sunday 2 Jan will be the first day of week two of that year. Strange, perhaps, but in line with some longstanding conventions.

If I understand you correctly, what you want to do is to call the week in which the first Monday of the year occurs week 1 and count the weeks forward from there?

That being the case, you should be able to determine the week number of a given date in any year by supplying the date to the following formula:

WeekofYear(YourDateHere) - Case(Int(DayofWeek(Date (1, 1, Year (Status(CurrentDate))) / 3), 1)

It simply lets the FileMaker convention pass for years in which the first of January is a Sunday or a Monday (because in those years the first Monday will be regarded as falling in week 1) but subtracts 1 from the week numbers in all years where the 1st January is between Tuesday and Saturday, because in those years, the First Monday is regarded by FileMaker as being in Week 2.

[BobWeaver]

I don't think that will account for a situation where Monday of week 1 is in the previous year, and therefore gives a result of 52 or 53.

[CobaltSky]

No, it sure won't - that wasn't the intention. It was my impression that what stefangs was wanting was that if the Monday of week one is in the previous year, it be counted as week 52 of the previous year and not regarded as part of the current year.

Perhaps I have misunderstood, but that is what it appears to me was being asked.

[stefangs]

yes, that's what i meant, just so i can be sure that whichever weekday happens to be on 1/1, that will be called week #1, regardless of whether the monday (which is my beginning of the week) happens to be 1/1 or still is in the old year.

thanks for all your responses - it's working now!