Change the Format of the Date in VBA

user_13613718 0
Viewed 25

I think this is a basic question but I do not find a way to solve my problem

I created this code :

Sub test()

Range("A11") = CDate(Evaluate("WORKDAY(TODAY(),-1)"))
Range("A11").NumberFormat = "yyyymmdd"
MsgBox Range("A11")


End Sub

But my problem is that the MsgBox do not take into account the NumberFormat "yyyymmdd", it shows "3/5/2021" instead of "20210305". It there a way to change the code so that when I write MsgBox Range("A11") it shows directly "20210305" ?

Thank you for your help.

format vba date
1 Answers

If you click the cell, you will most likely see 3/5/2021 as the value of the cell. Because it hasn't really changed, just the cell formatting.

MsgBox Range("A11") is the same as MsgBox Range("A11").Value, which will give you the value of the cell, without the formatting.
Try using MsgBox Range("A11").Text instead.

user_71311220
Related