Get the week number from a given date

Question

Examples:

'DD/MM/YYYY
"1/1/2009" should give `1`
"31/1/2009" should give `5`
"1/2/2009" should also give `5`

Format("1/2/2009", "ww") returns 6.

So, how can I get the correct result?

Answer 1

It's doing two things here which don't match your expectations, I think: Assuming you want the week with Jan 1 in as week 1, and using Sunday as first day of the week So it has week 1 running from Sunday 28th December 2008 to Saturday 3rd Jan 2009.

Week 6 would begin on Sunday 1st Feb by this method.

The ISO standard is for week 1 to be the one containing 4 days of January, or the first Thursday of the year (different ways of expressing the same thing). You can specify this method of calculation and the first day of the week:

Format(SomeDate,"ww",vbMonday,vbFirstFourDays)

see here for syntax:

https://support.office.com/en-US/article/Format-Function-6F29D87B-8761-408D-81D3-63B9CD842530

Answer 2

This might work: Format(YourDate, "ww",vbMonday)

Answer 3

Regardless of the day of the week your week starts on, you need to pass unambiguous date values. "31/1/2009" can only be one date (Jan 31st), but "1/2/2009" could be Jan. 2 (US style) or Feb. 1st (everybody else who has more sense that we USAns).

In this case, I'd use DateSerial() to make sure the date is not misinterpreted:

  Format(DateSerial(2009,2,1), "ww", vbMonday)

While this is not causing your problem, because Access helpfully utilizes your system's localized date settings, I think it's something you should do anyway. You certainly are forced to do so in SQL in Access, so I don't think it's a bad habit in code and expressions.