Calculating week number

[KevinNewtoAccess]

I have seen this topic raised a couple of times but, as yet, cannot get a working solution.

I need to get the weeknumber of any given date.

I have looked back over previous solutions to this and am using the following function

Public Function WeekNum(Mydate)
WeekNum = Format(Mydate, "ww&quot
End Function

However, this appears to be giving some wrong answers! When I feed this with the date 8/12/2000 it returns weeknumber 50 - my diary (and my customers) shows this as week 49. When trying todays date 16/3/2001 it returns 11 which is correct.

Any suggestions?

 

[LarryDeLaruelle]

Kevin:

I think the answer might be the system date format.

You are using the European date format (DD/MM/YY); the date 8/12/2000 is ambiguous -- it could be read as August 12, 2000 or December 8, 2000.

I can't say for certain that this is where the problem is occuring (especially since the week is off only by one), but it is a place to check.

Another possibility may be the added day for a leap year. However, since December 12, 2000 was a Thursday I don't see how that would cause the result to jump a week.

Hope this helps.
Larry De Laruelle
larry1de@yahoo.com

 

[hellferret]

This is due to Access being weird.

1/1/2000 was a Saturday, so week 1 was 2 (or 1 depending oin your view) days long. On most calenders this is not marked as week 1 (because it is not the 1st working week of the year).

Access however numbers it as week 1. So the whole year will be a week out.

So: If the year starts on a Saturday, Access will get the week number wrong.

 

[MichaelRed]

Not sure this will solve it, but ...

look at the "regional settings" it may be able to shed some light. There are also some properties, "First Day of Week" and "First Week of Year" which may provide some ability to "tune" the output of date thinggyysssss in Ms. Acceates.

I 'reset' the date to be in "U.S." format, set up the process and used:

? datediff("ww", #1/1/2000#, #12/8/2000#,vbUseSystemDayOfWeek, vbFirstJan1)
49

but leaving out the optional attributes give me the same results, so somewhere in your system you have apparently set up a regional setting which conflicts with the date settings which Ms. Access uses for the calc.

Otherwise, a 'roll-your-own' soloution isn't really all that difficult:

MyWeek =Round(DateDiff("d", #1/1/2000#, #12/8/2000#)/7, 0)
? MyWeek
49

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over