Calculate Week number correctly in Visual Foxpro
Silly Week() function in Foxpro doesn’t work like it should! The year 2009 was very long… So it had a week 53. I think here in Holland we use the method that the larger part of the week should be in the new year to call it week number one. (Silly me looked over the 3rd parameter. See the easy solution below under Update).
So according to the help on the Week() function we select nFirstWeek 2 (The larger half (four days) of the first week is in the current year.).
Foxpro Week(Date(2009,12,31),2) returns week 52! That is absolutely wrong. A search on the Internet brought me a solution that works for me. It was found on Fox Wiki. This is the very impressive code:
FUNCTION GetWeekNo LPARAMETERS ldDate lnJulian = VAL(SYS(11,ldDate))+1 lnDay4 = MOD(MOD(MOD((lnJulian+31741 - MOD(lnJulian,7)),146097),36524),1461) lnLeap = INT(lnDay4/1460) lnDay1 = MOD(lnDay4-lnLeap,365) + lnLeap RETURN INT(lnDay1/7)+1 ENDFUNC
Thanks go to theRambler who proposed this on TekTips.
UPDATE
I stand corrected… Nard van Gentevoort emailed me to explain the VFP Week() function wil give me the right week number. But first I have tot tell it what day the week starts on. Silly Americans start the week on Sunday “at the seventh day he relaxed and saw all was good”.. doesn’t ring a bell? So because we do things right and start off our week at Monday, the full function call in the Netherlands should be: Week(date,2,2). Tested WEEK(DATE(2009,12,31),2,2) and it gives 53 like I expect it to. Thanks Nard !