Get the week number from a given date

A

8

10

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?

Allelomorph answered 30/10, 2009 at 13:3 Comment(1)

It's simply counting sunday as the first day of the week, not monday. – Nowell 30/10, 2009 at 13:6

E

29

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

Engstrom answered 30/10, 2009 at 13:24 Comment(4)

+1, this is the complete answer. I hope you don't mind the cosmetic edit. – Tahiti 30/10, 2009 at 13:31

Not at all, and I now got round to updating my profile info as well so I have a proper name (I just popped by from superuser.com and signed up to answer this one) – Engstrom 30/10, 2009 at 13:38

AdamV, you clarified the issue. Thank you. – Allelomorph 30/10, 2009 at 14:2

vbMonday and vbFirstFourDays both = 2 if you are querying access from another source. – Rigsdaler 26/5, 2016 at 13:5

C

6

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.

Cursive answered 30/10, 2009 at 23:40 Comment(0)

H

5

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

Heptarchy answered 30/10, 2009 at 13:8 Comment(4)

@Sosh: I am curious to see what the OP expects for 01/05/09 (Jan 5th 09)? I think OP might expect Jan 5th to be week 1 whereas it will be week 2 as per your example. – Weatherboard 30/10, 2009 at 13:30

There is also a firstweekofyear argument for the format function: vbUseSystem, vbFirstJan1, vbFirstFourDays, vbFirstFullWeek – Bereniceberenson 30/10, 2009 at 13:34

@shahkalpesh, I expect 2 and I get 2. – Allelomorph 30/10, 2009 at 13:38

@Nick: Sosh is right in that case. The week starts from Monday as per your example. – Weatherboard 30/10, 2009 at 13:44

T

2

"Correct result" depends on the locale. Maybe VBA will let you pick a calendar-system, otherwise you're pretty much out of luck.

Note that First-Day-On-xxDay isn't your only problem. There is also variation on what a complete week is so Week 1 in one system could be Week 53 of the previous year in another system.

So test thoroughly and don't be seduced to "correct by 1".

Tahiti answered 30/10, 2009 at 13:13 Comment(1)

thanks. About your note: the week number, for me, isn't critical. It only accompany a date field. – Allelomorph 30/10, 2009 at 13:30

R

1

There is a whole standard for week numbers: ISO-8601

http://en.wikipedia.org/wiki/ISO_8601#Week_dates

Repulsive answered 30/10, 2009 at 13:17 Comment(1)

Yes, but not all clients (countries) use that. – Tahiti 30/10, 2009 at 13:27

S

1

I had the same problem.

It showed week 53 and week 1, yet days in week 53 and week 1 are all in week 1

I first tried changing the date format in the Access Query to this:

OrderWeek: Format([OrderDate],"yyyy-ww",1,3) <-- But it did not do the trick. You get dates like 2014-52 for week 52 and 2015-52 where it was week 1 before.

Also the sorting was not how I liked. It sorted the data as 2014-1, 2014-11, 2014-2 etc. I want it to show as 2014-01, 2014-02 .. 2014-11 etc.

So here is the new code to display both the year and the week correctly in an Access Query:

ActualWeek: IIf(DatePart("ww",[SomeDate])=53,DatePart("yyyy",[SomeDate])+1,DatePart("yyyy",[SomeDate])) & "-" & IIf(DatePart("ww",[SomeDate])=53,"01",IIf(DatePart("ww",[SomeDate])<10,"0" & DatePart("ww",[SomeDate]),DatePart("ww",[SomeDate])))

This now shows any days from week 53 as being part of week 1

Sevigny answered 14/1, 2015 at 11:28 Comment(0)

O

0

If sunday is the first day of the week (as it is in some locales) then 6 is the correct weeknumber for "1/2/2009" (february 1. 2009)

Openhanded answered 30/10, 2009 at 13:18 Comment(0)

E

0

In terms of the sorting, I had the same issue and used this code to resolve it:

IIf(Format([SomeDate],"ww")<10,Format([SomeDate],"yyyy-") & "0" & Format([SomeDate],"ww"),Format([SomeDate],"yyyy-ww"))

If the week number is less than 10, add a zero, else leave it as is.

Now the sorting is fine. Hope this helps somebody.

Ebony answered 14/2, 2023 at 7:19 Comment(0)

Recommended topics

Hot tags