In VBA I want to convert a date as 03/11/2017(DD/MM/YYYY) into the week number for that date.
Until now I have the following code:
'geting the date out of the string
HeadlineTemp = Mid(VRHeadline, InStr(VRHeadline, "[") + 1, 10)
'switch "." to "/"
HeadlineTemp = Replace(HeadlineTemp, ".", "/")
'convert to a date
FristVRFirstKW = CDate(HeadlineTemp)
Now, I need a function to convert that date into the week number of the year. First week day is Monday.
WeekNum uses the week containing January 1 as the first week of the year. The result from this function can range from 1 to 54. ISOWeekNum uses the week containing the first Thursday of the year as the first week of the year. This follows the ISO 8601 date and time standard definition for week numbering.
The WEEKNUM function takes a date and returns a number between 1 and 54 that corresponds to the week of the year. By default, the WEEKNUM function starts counting on the week that contains January 1 and increments week numbers on Sunday. The WEEKNUM function accepts two arguments, serial_num and return_type.
The ISOWEEKNUM Function[1] in Excel is a Date & Time Function used for finding out the ISO week number of the year for the given date value (e.g., week number 37 of 52 weeks in a year). The function was introduced in MS Excel 2013.
To make the Week Number with Monday as a first day, use the following:
WorksheetFunction.WeekNum(now, vbMonday)
Using VBA, to convert a date into an isoWeeknumber, you merely need the DatePart
function (where DT is the date of interest):
isoWeekNumber = DatePart("ww", DT, vbMonday, vbFirstFourDays)
If you want to use other definitions than that specified in ISO 8601, investigate some of the other options for FirstDayOfWeek
and FirstWeekOfYear
NOTE
As pointed out by @Mike85, there is a bug in DatePart
(and also in the Format
) function wherein Monday may be erroneously given a weeknumber of 53 when it should be 1.
There are a variety of workarounds.
In Excel 2013+ (Excel for Mac 2011+) you can use for the ISO Weeknumber:
isoWeekNumber = WorksheetFunction.isoWeekNum(dt)
For earlier versions, you can test the Monday and adjust it if necessary, or you can write a separate routine.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With