VBA code to get week number from given date

Question

I am working with Excel 2010.

I wish to convert a given date from the format mm/dd/yyyy to the format Wyy"weeknumber"

For example, 4/10/2017 would become W1715, since it is week 15 of 2017.

The below shown image is of the excel table I am working on. I want to convert the dates in column LT Verification - Planned Date to the week number format mentioned above, in column LT Verification - Planned Week Numbers.

Edit: Because this is part of a larger VBA process, I need it to be in VBA, not a cell formula.

I have written the following code:

Public Sub WeekNumbers()

Dim lastRow As Integer
    lastRow = Range("A1:AZ1").Find("*", , , , xlByRows, xlPrevious).Row

Dim myRange As Range
    Set myRange = Range("A1:AZ1" & lastRow)

Dim myCell As Range
    For Each myCell In myRange

myCell.Offset(0, 1).Value = "W" & Right(Year(myCell.Value), 2) & Application.WorksheetFunction.WeekNum(myCell.Value)**

 Next myCell

 End Sub

This code gives me error at myCell.Offset(0, 1).Value = "W" & Right(Year(myCell.Value), 2) & Application.WorksheetFunction.WeekNum(myCell.Value)

Here I have a excel workbook which will be updated every week. So, each time it is updated, it runs a macro to import data from another file & perform the week number activity & create a pivot table.

So, the sheet name changes every week. Also, the column headers may be in different columns in different weeks. Also, the number of rows may also change every week.

So, I need to specify column & row range dynamically based on that weeks data. And have the week numbers in the column based on the column header rather than the column name (A or B or Z...)

Answer 1

This can be achieved easily with a cell formula:

="W" & RIGHT(YEAR(A1),2) & WEEKNUM(A1)

Where A1 can be replaced by the cell containing the date.

In VBA this is equivalent to

With Thisworkbook.Sheets("Sheet1")
    .Range("A2").Value = "W" & Right(Year(.Range("A1").Value), 2) & Application.WorksheetFunction.WeekNum(.Range("A1").Value)
End With

---

Edit:

To fill an entire range, you could loop over the cells, apply the VBA calculation as above.

Dim myRange as Range
Set myRange = Thisworkbook.Sheets("Sheet1").Range("A1:A10")

Dim myCell as Range
For Each myCell in myRange
    myCell.Offset(0,1).Value = "W" & Right(Year(myCell.Value), 2) & Application.WorksheetFunction.WeekNum(myCell.Value)
Next myCell

There are many methods for finding the last row in a range, so I'll leave that to you if you don't know your range.

---

Edit 2: in response to your error edit.

You have used the following line to define your range:

Set myRange = Range("A1:AZ1" & lastRow)

Let's imaging you have lastRow = 20, you now have

myRange.Address = "A1:AZ120"

Which is clearly wrong, you shouldn't have the 1 after the AZ. Also I don't know why you've gone to column AZ, if all of your date data is in column A, you should use

Set myRange = Range("A1:A" & lastRow)

The loop you've implemented uses an offset, so the values in column B are changed to reflect those in column A. You can't then set column C according to column B!

Answer 2

In VBA, you can get your string by using the Format function. "\Wyyww" is the format you are looking for, where \ is used to escape the interpretation of the first W character and to take it as a litteral.

myCell.Offset(0,1).Value = Format(myCell.Value, "\Wyyww")

More

You have to setup correctly the range for your loop. If your dates are in some column with header "LT Verificiation - Planned Date", you can try this:

Dim ws As Worksheet
Set ws = ActiveSheet ' <-- you can change this into something explicit like Sheets(someIndex)...

Dim myCell As Range
Set myCell = ws.Rows(1).Find("LT Verificiation - Planned Date")
For Each myCell In ws.Range(myCell.Offset(1), ws.Cells(ws.Rows.Count, myCell.Column).End(xlUp))
    If IsDate(myCell.value) Then myCell.Offset(, 1).value = Format(myCell.value, "\Wyyww")
Next myCell

Answer 3

I don't think you need VBA for this, try this formula:

=RIGHT(YEAR(A1),2)&WEEKNUM(A1)&"W"

Of course, if you insist on VBA, you can always turn Excel Formulas into VBA code. In this case:

Dim rngInput As Range
Dim rngOutput As Range
With Application.WorksheetFunction
    rngOutput.Value = .Right(.Year(rngInput.Value), 2) & .Weeknum(rngInput.Value) & "W"
End With

Or you may even set the Formula, and Insert the Value, like this

Dim rngInput As Range
Dim rngOutput As Range
rngOutput.Formula = "=RIGHT(YEAR(" & rngInput.Address(False, False) & "),2)&WEEKNUM(" & rngInput.Address(False, False) & ")&""W"""
rngOutput.Value = rngOutput.Value