Question posted 2009 · +8 upvotes
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?
Accepted answer +22 upvotes
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
Top vba Q&A (6)
- Difference between Visual Basic 6.0 and VBA +122 (2009)
- VBA – how to conditionally skip a for loop iteration +116 (2011)
- VBA: Test if string begins with a string? +53 (2013)
- html parsing of cricinfo scorecards +47 (2012)
- Code to loop through all records in MS Access +46 (2011)
- Access VBA | How to replace parts of a string with another string +44 (2011)
vba solutions on this site
— top 20%.