Week of the year

[Peter (duksis3)]

FM function WeekOfYear return wrong value for year 2010 for example. 1 Jan 2010 is week 53 (year 2009) not week 1 and 8 Jan 2010 is week 1 not 2.

Normally the 1st week of the year is the week when 1Jan is after Wednesday.

Or I'm wrong?

[comment]

Answered here:

http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&thread.id=35344

[Peter (duksis3)]

Answer isn't correct.

Look here http://www.onlineconversion.com/day_week_number.htm and then on FM funciton result - results are different.

[LaRetta]

Well, I don't know correct answer ...

When I put 1/1/2010 (mm/dd/yyyy) into that calculator, it says US Standard 52 and Europe/ISO standard is 53.

When I create calculation in FileMaker (FMPA 9.0v3):

WeekOfYear ( Date ( 1 ; 1 ; 2010 ) ), it displays 1

I don't particularly trust either source (FM or that online site; both can be wrong and have errors in their calculation engines) but I DO trust Comment in his understanding of dates.

[IdealData]

There different "standards" for date/week calculation. See this one

http://en.wikipedia.org/wiki/ISO_week_date

Simple?

Edited December 30, 2009 by Guest

[comment]

Answered here:

http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&thread.id=35344

Could we please have this discussion in one place only?

[Peter (duksis3)]

That is what I'm talking about.

FM function - Week of Year - is without any meaning and is useless.

And explanation is simple - date definitions and calculations have so many variants - not only in world around but in IT also - for example MS and Apple use different computer calendar starting dates:

MS DOS - 1.Jan 1980.g., IBM -1.Jan 1900.g., MacOS X – 1.Jan 1904.g.,Windows – 1.Jan 1601.g..

This obstacle produce mess - when you import the same date from Excel into FM - not the same result for Win and OS X based FM.

And worse - instead of date you get a day number from computer calendar start - you need to make more complicated date calculation for FM files to use on different computers.

Some detailed reflection I have provided here: http://www.infovadiba.lv/2008/09/12/datubaze-nemakot-progarammet/ - blog is in Latvian but video is from English OSX.

So FM have provided "overall" WeekOfYear function saying (from FM Help): "Fractions of weeks occurring at the beginning or end of the year count as full weeks, so the WeekOfYear function returns values 1 through 54."

Don't use in calendars directly - modify before.

[Peter (duksis3)]

Just to explain calculation for correct WeekOfYear:

Correction=If ( DayOfWeek ( 1/1/Year) = 7 or DayOfWeek ( 1/1/Year) ≥ 1 and DayOfWeek ( 1/1/Year) ≤ 3 ; 0; 1). This can be stored as global variable via FileOpen Script.

Then WeekOfDayCorrect=WeekOfDay (Date)-Correction.

For the week containing 1/1/Year must be provided another calculation or Conditional Format.