Skip to content

Formatting Week Numbers

These are some different settings user’s can use to yield the week number in the format they want.

Q:  Terry Smith writes that the expression

Format(Now(),”ww”)
 

Which used to yield the correct week number, no longer does so (in early 2005).

A:  This is not a bug, but a result of the Format function using default settings for the first day of the week and the first week of the year that are not what you want (most likely it is picking up the system settings).  Since Jan. 1 is a Saturday in 2005, you will get different values depending on how you set the firstdayofweek and firstweekofyear arguments.  There are three named constants you can use for the firstweekofyear argument, depending on whether you consider the first week of the year to be the first full week (seven days), the first week with at least four days, or just the week (perhaps only a single day) in which Jan. 1 occurs.  The latter is the default choice.

The following expression yielded a week number of 3 when I ran it on Jan. 16:

Format(Now(),”ww”, vbMonday, vbFirstJan1)
 

And this expression yielded 2:

Format(Now(), “ww”, vbMonday, vbFirstFullWeek)

About this author