Jump to content

calculating the week number


This topic is 7867 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

> 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 :-)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 7867 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.