How do you calculate the week number in a year of a given date?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello All,

I'm sure that someone have this function in one of they
database. I need a function that will calculate the week
number in a year of a given date in microsoft access. Thnx!
 
I'm sure that someone have this function in one of they
database. I need a function that will calculate the week
number in a year of a given date in microsoft access.
Click to expand...

This is the most generally reliable VBA expression for determining the
week number according to ISO standards. Specify all the optional
parameters to make sure user settings don't trip you up.

CInt(Format(TheDate, "ww", vbMonday, vbFirstFourDays))

Unfortunately, a bug makes Format() return the wrong value for some
days; 29 Dec 2003 is the next one to come. A description of the bug
is in KB article Q200299, "Format or DatePart Functions Can Return
Wrong Week Number". That article includes a function that works
correctly (according to ISO 8601).

Using a table instead of a function might make your application more
useful. A table containing the week numbers for every day for the
next 100 years would have less than 37,000 rows. It can be used
directly by any client software, not just Access. And using a table
makes it dead simple to determine the dates covered by a given week
number.
 
Back
Top