• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

DatePart("ww", "03/22/2009") Returns it as Week #13 when it should be #12?

Why does
DatePart("ww", "03/22/2009")
Return it as Week #13 when it should be #12?
The first day of the week is defaulted to Sunday...which should still return 12, not 13.

Does anyone have any ideas on why.
  • 2
  • 2
1 Solution
Patrick MatthewsCommented:
Hello 16spam16,

The optional 4th argument in DatePart determines how to define the first "week" of the year.  Using this in
the Immediate window gets 12:

?DatePart("ww", "03/22/2009", vbSunday, vbFirstFullWeek)


16spam16Author Commented:
Now that did work, but then i changed it and tried this day. and it was wrong...

DatePart("ww", "03/16/2008", vbSunday, vbFirstFullWeek)

returns 11, when it should return 12.

I changed it to

DatePart("ww", "03/16/2008", vbSunday, vbFirstFourDays) returned 12
DatePart("ww", "03/22/2008", vbSunday, vbFirstFourDays) returned 12

Is that correct? the answers are correct.

What I am doing is selecting the week via the VB6 Calendar control with my weeks starting on Sunday.
Then I use DatePart to get the week number when i do my processing. It is funny on I get different week numbers.

What are your thoughts?
Patrick MatthewsCommented:
From the VBA help (works the same way in VB6), these are the values you can use:

Constant      Value      Description
vbUseSystem      0      Use the NLS API setting.
vbFirstJan1       1      Start with week in which January 1 occurs (default).
vbFirstFourDays      2      Start with the first week that has at least four days in the new year.
vbFirstFullWeek      3      Start with first full week of the year.

See which definition matches best to your expectations.

16spam16 said:
>>What are your thoughts?

My thought is that week numbers are usually a waste of time, because everyone has a different idea of how to
define them :)

Seriously, if none of these definitions meet your needs, then you will need to use a calendar table or something
to explicitly define your week numbers.
16spam16Author Commented:
I have looked through the VBA help and i couldnt find anything that says how the "MonthView" control figures out week numbers. I think if i knew that id know what to pick.

I have run a few more dates and it seems as though

DatePart("ww", mydate, vbSunday, vbFirstFourDays)

seems to have done the trick. atleast it appears to anyways.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now