CODEWITHSUNDEEP
vbaexcelexcel-2013
this.guy
this.guy

Reputation: 91

Extract weeknumber from a date in Excel 2013 using VBA

I've checked around the web, but nothing seems to help my case :(

I basically want to extract the weeknumber from a date. I have successfully extracted the two last digits of the year from the same cell using this code:

Right(Year(wbThis.Sheets("Availability").Range("C5").Value), 2)

(wbThis is defined as a workbook)

But the weeknumber has yet to appear for me.

So you can check yourself if it's correct, the date in the cell C5 of wbThis workbook is 

2016-02-12 (YYYY-MM-DD)

Which is equivalent to Weeknumber 06. (I want 06 if it's 6 but i'm sure you could figure that out :P)

Big thanks, best regards.

(PS. Bear in mind that the FORMULA of the cell C5 says 2016-02-12 - the cell actually just shows 12-feb. So the .Value will return 2016-02-12)

Upvotes: 1

Views: 454

Answers (2)

user4039065
user4039065

Reputation:

The ww number format mask can be used to retrieve the equivalent of the WEEKNUM function.

Debug.Print Format(ThisWorkbook.Sheets("Availability").Range("C5").Value, "ww")
Debug.Print Right("0" & Format(ThisWorkbook.Sheets("Availability").Range("C5").Value, "ww"), 2)

For 01-Mar-2016, this returns 10. I get 7 not 6 for 12-Feb-2016 in both the ww format mask and the WEEKNUM function. If you want to return 6 (or 06) then you will have to use the ISOWEEKNUM function¹.

Debug.Print WorksheetFunction.IsoWeekNum(ThisWorkbook.Sheets("Availability").Range("C5").Value)
Debug.Print Right("0" & WorksheetFunction.IsoWeekNum(ThisWorkbook.Sheets("Availability").Range("C5").Value), 2)

¹ The ISOWEEKNUM function was introduced with Excel 2013. It is not available in earlier versions.

Upvotes: 1

Jordan
Jordan

Reputation: 4514

You can use the WEEKNUM function in your formula in addition to an IF function to concatenate a leading 0 if the length of the week is 1:

If Len(WorksheetFunction.WeekNum(wbThis.Sheets("Availability").Range("C5").Value))=1 Then
    Debug.Print 0&WorksheetFunction.WeekNum(wbThis.Sheets("Availability").Range("C5").Value)
Else
    Debug.Print WorksheetFunction.WeekNum(wbThis.Sheets("Availability").Range("C5").Value)
End if

Upvotes: 2

Related Questions