Getting Weeknumber off of an arbitrary date

[WardenBerret]

Hi. Glad to be apart of the community even if for a short period :D

So, my problem...

I want to get the weeknumber from a date in a cell using an excel macro.
I have this code right now, but I keep getting a compiler error

If Len(WorksheetFunction.WeekNum(wbThis.Sheets("Availability").Range("C5").Value)) = 1 Then    
    wbTarget.Sheets("Sheet1").Range("C6") = "w." & Right(Year(wbThis.Sheets("Availability").Range("C5").Value), 2) & "0" & WorksheetFunction.WeekNum(wbThis.Sheets("Availability").Range("C5").Value) & ".5"
Else
    wbTarget.Sheets("Sheet1").Range("C6") = "w." &  Right(Year(wbThis.Sheets("Availability").Range("C5").Value), 2) & WorksheetFunction.WeekNum(wbThis.Sheets("Availability").Range("C5").Value) & ".5"
End If

The error I get highlights the first .WeekNum expression and it says:

[COLOR=#222426][FONT=Consolas]Compile error: Variable Required - can't assign this to this expression[/FONT][/COLOR]

I basically want the output in

wbTarget.Sheets("Sheet1").Range("C6")

to be in the format w.YYWW.5 (YY = last digits of the year, which works! & WW=weeknumber with "0" if the week is between 1 - 9 ,this does not work!​)
As I said, the last digit of the year - which is taken from the same Date cell as the weeknumber i want - is working.

Big thanks, best regards.

 

[steve the fish]

Try this. You will have to add your sheet names etc but it gets you on right track:

mydate = Range("C5")
wk = WorksheetFunction.WeekNum(mydate)

Range("C6") = "w." & Right(Year(mydate), 2) & Format(wk, "00") & ".5"

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Those long lines would fidget me to death. Anything that goes off the right hand side of the screen grates. OK, it is a personal thing and not necessarily wrong.

So I might do it more like this:

Sub WeekNum()

    Dim wbThis      As Workbook
    Dim wbTarget    As Workbook
    Dim wsA         As Worksheet
    Dim ws1         As Worksheet
    Dim r           As Range
    
    Set wbThis = ThisWorkbook
    Set wbTarget = ThisWorkbook    ' Note: Change this line
    Set wsA = wbThis.Worksheets("Availability")
    Set ws1 = wbTarget.Sheets("Sheet1")
    Set r = wsA.Range("C5")

    ws1.Range("C6") = "w." & Format(r, "YY") & Format(DatePart("WW", r), "00") & ".5"

End Sub

Note: I used the same workbook for wbTarget and wbThis so you will need to change at least one of them.

 

[WardenBerret]

Hi and welcome to the MrExcel Message Board.

Those long lines would fidget me to death. Anything that goes off the right hand side of the screen grates. OK, it is a personal thing and not necessarily wrong.

So I might do it more like this:

Sub WeekNum()

    Dim wbThis      As Workbook
    Dim wbTarget    As Workbook
    Dim wsA         As Worksheet
    Dim ws1         As Worksheet
    Dim r           As Range
    
    Set wbThis = ThisWorkbook
    Set wbTarget = ThisWorkbook    ' Note: Change this line
    Set wsA = wbThis.Worksheets("Availability")
    Set ws1 = wbTarget.Sheets("Sheet1")
    Set r = wsA.Range("C5")

    ws1.Range("C6") = "w." & Format(r, "YY") & Format(DatePart("WW", r), "00") & ".5"

End Sub

Note: I used the same workbook for wbTarget and wbThis so you will need to change at least one of them.

Hi, i actually tried to define variable such as:

theDate = wbThis.Sheets("Availability").Range("C5")

But i kept getting the error:

Variable Error: Variable not defined

and it highlighted theDate
So i gave up and just used the exact sheet references..

Try this. You will have to add your sheet names etc but it gets you on right track:

mydate = Range("C5")
wk = WorksheetFunction.WeekNum(mydate)

Range("C6") = "w." & Right(Year(mydate), 2) & Format(wk, "00") & ".5"

Thanks, it works - however.. the date in Cell C5 is 12-feb-16 so it should write out 06 as weeknumber... but it writes out 07 as the weeknumber
how can i fix this? I know in excel 2013 u can use ISOWeekNum - but im on excel 2010

Thanks.

 

[steve the fish]

WEEKNUM takes two arguments. Firstly a date then a 2nd part which tells excel which system to use. So try:

wk = WorksheetFunction.WeekNum(mydate,21)

where 21 is the UK type date system with a week starting on a Monday.

 

[WardenBerret]

WEEKNUM takes two arguments. Firstly a date then a 2nd part which tells excel which system to use. So try:

wk = WorksheetFunction.WeekNum(mydate,21)

where 21 is the UK type date system with a week starting on a Monday.

It worked, great!