CODEWITHSUNDEEP
excelvbadateformat
Afshin Davoudy
Afshin Davoudy

Reputation: 181

Show week number of the year in custom format

I'm wondering if there is any custom date format (Excel or VBA) that shows week number (of the year) in a date.

Here is to clarify what I mean: Assume

A1 = "02/03/2020  13:19:57"

I have This custom format on A1:

[$-en-US]ddd, mmm dd, hh:mm

The result is:

Mon, Mar 02, 13:19

So I'd like to have week number at the end:

Mon, Mar 02, 13:19, 10

BTW need "A1" to remain as Date

Appreciate any help.

Solved Thanks to @FaneDuru

WeekNum = WorksheetFunction.WeekNum(ActiveCell, vbMonday)
With ActiveCell
    .NumberFormat = "[$-en-US]ddd, mmm dd, hh:mm" & """, W" & WeekNum & """"
    Debug.Print "Week num: " & WorksheetFunction.WeekNum(.Value)
    Debug.Print Year(.Value)
End With

Upvotes: 1

Views: 4310

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

Try the following code, Please. It will remain Data format, but the week number will be something built. It can be extracted from the date...

Sub testNumberFormatSpec()
  With ActiveCell
    .Formula = "=Now()"
    .NumberFormat = "ddd, mmm dd, hh:mm"" Week " & _
       WorksheetFunction.WeekNum(ActiveCell.value, vbMonday) & """"
     Debug.Print "Week num: " & WorksheetFunction.WeekNum(.value)
     Debug.Print Year(.value)
  End With
End Sub

What I tried to suggest is the fact that this format cannot by just filled down for other cells. It must be built using the above code. The week number is added as string at the end of the date format. It can be extracted from the existing date value, but not to be applied like format to another range. In fact, the code can be introduced in Worksheet_Change event in order to adapt the format of each changed cells... Of course, in a previously defined range.

Upvotes: 1

Related Questions