Convert from current date to the number of weeks in a calendar yea

[Vi]

Hi all,

I was wondering if someone can help me in regards to creating a query that
will take the current date and convert it to the number of weeks it is in the
calendar year.

For example: If today is Jan 7, 2008, I would like to have a formula that
will calculate that it is week 2.

You help is greatly appreciated.
Vi

 

[Douglas J. Steele]

format(#07-jan-08#,"ww")

Of if you want a numeric value returns (Format returns a string version of
the number),

DatePart("ww", #07-jan-08#)

Note to that there are optional parameters you can pass to both the Format
and DatePart functions that control what day is considered to be the first
day of the week, and how the first week of the year is calculated (first
week that has Jan 1 in it, first week that has 7 days in January in it and
so on). Check the Help file for details.

 

[James A. Fortune]

Hi all,

I was wondering if someone can help me in regards to creating a query that
will take the current date and convert it to the number of weeks it is in the
calendar year.

For example: If today is Jan 7, 2008, I would like to have a formula that
will calculate that it is week 2.

You help is greatly appreciated.
Vi

If you use the DatePart() or Format() functions with "ww" and the
function uses vbMonday and vbFirstFourDays, be sure to check for the
following bug:

BUG: Format or DatePart Functions Can Return Wrong Week Number for Last
Monday in Year

http://support.microsoft.com/kb/200299

It turns out that the code to get this right for all situations is much
tougher than it looks so I don't blame Microsoft as much for that one.

James A. Fortune
(e-mail address removed)