VBA code to show date & weeknum

[hrayani]

Hello Friends,

I want a vba code to do following

As soon as I enter a date in any cell in Range("A1:A10") then the code should show the date along with week # in that particular cell

For example if I enter 25/Mar in cell A1 Then I want the cell A1 to change to show 25/Mar (Week 13)

Regards,

Humayun

 

[rollis13]

You could have a try with this macro (to be pasted in the sheet's module):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo terminate                       'required for irregular leap year inputs and other cases
    If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not IsEmpty(Target) And IsDate(Target) Then Target = Format(Target, "dd/mmm") & " (Week " & WorksheetFunction.WeekNum(Target) & ")"
terminate:
    Application.EnableEvents = True
End Sub

 

[hrayani]

You could have a try with this macro (to be pasted in the sheet's module):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo terminate                       'required for irregular leap year inputs and other cases
    If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not IsEmpty(Target) And IsDate(Target) Then Target = Format(Target, "dd/mmm") & " (Week " & WorksheetFunction.WeekNum(Target) & ")"
terminate:
    Application.EnableEvents = True
End Sub

Hi rollis13,

Thanks for the code - it is working but could you pls look into the below 2 points

1) I entered "1/1" & it changed to "1-Jan-24 (Week 1)" but when I edited the cell & changed the day to 15 then also it shows the same week number "15-Jan-24 (Week 1)" whereas, it should show week 3 like "15-Jan-24 (Week 3)" . In other words I mean to say that the week number does not change when you edit the cell. It only changes when you enter a new day into the cell

2) what day of the week the code is considering to be the 1st day & can we change it like the weeknum formula allows us to do

 

[rollis13]

1) You are not allowed to edit a cell because the macro checks and expects only a date input; when you edit the cell you are now editing a text input. Always re-enter the date if there is something wrong.
2) Almost like formulas, but only 1 and 2 are allowed, see LINK.

By the way, I think you should change the format of the date in the macro (maybe it's just an international formatting issue) because, as it is ("dd/mmm"), when you enter 1/1 the output should be 01/Jan (Week 1) as you asked in post #1 and not 1-Jan-24 (Week 1), see LINK.

 

[hrayani]

1) You are not allowed to edit a cell because the macro checks and expects only a date input; when you edit the cell you are now editing a text input. Always re-enter the date if there is something wrong.
2) Almost like formulas, but only 1 and 2 are allowed, see LINK.

By the way, I think you should change the format of the date in the macro (maybe it's just an international formatting issue) because, as it is ("dd/mmm"), when you enter 1/1 the output should be 01/Jan (Week 1) as you asked in post #1 and not 1-Jan-24 (Week 1), see LINK.

Sorry, I did not informed you - I changed the format from "dd" to "d"

Almost like formulas, but only 1 and 2 are allowed, see LINK.
Can yo pls let me know which part of the code could i edit to change 1 & 2

 

[rollis13]

This would be the Arg2 if week begins on Monday instead of Sunday.
WorksheetFunction.WeekNum(Target, 2)

 

[hrayani]

This would be the Arg2 if week begins on Monday instead of Sunday.
WorksheetFunction.WeekNum(Target, 2)

Thanks

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.