stefangs Posted November 3, 2002 Share Posted November 3, 2002 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 More sharing options...
Vaughan Posted November 4, 2002 Share Posted November 4, 2002 Huh? I just tried 1/1/2003 and the WeekOfYear function returned 1. Link to comment Share on other sites More sharing options...
stefangs Posted November 4, 2002 Author Share Posted November 4, 2002 > 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 More sharing options...
BobWeaver Posted November 4, 2002 Share Posted November 4, 2002 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 More sharing options...
CobaltSky Posted November 5, 2002 Share Posted November 5, 2002 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 More sharing options...
BobWeaver Posted November 5, 2002 Share Posted November 5, 2002 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. Link to comment Share on other sites More sharing options...
CobaltSky Posted November 5, 2002 Share Posted November 5, 2002 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 More sharing options...
stefangs Posted November 5, 2002 Author Share Posted November 5, 2002 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 More sharing options...
Recommended Posts
This topic is 7970 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 accountSign in
Already have an account? Sign in here.
Sign In Now