Format of Date displaced in Excel cell

New Contributor

I want to store the current date in day/month/year format for later use with the code below:


Sub ShowDate()


Dim myDate As String

myDate = Format(Now, "DD/MM/YYYY")

ThisWorkbook.Sheets(1).Range("R20").Value = myDate


End Sub


I run the macro on 1st August 2022, my computer region setting is English (United Kingdom), but it shows 8th January 2022 instead. Why?


2 Replies
have the macro change the format of the cell.
I think it's this:
ThisWorkbook.Sheets(1).Range("R20").NumberFormat = "DD/MM/YYYY;@"


Thanks. But adding NumberFormat does not solve my problem.


Sub ShowDate()


Dim myDate As String

myDate = Format(Now, "DD/MM/YYYY")


Range("A1").Value = "Next Report Date = This Report Date + 7 "

Range("B2:B3").NumberFormat = "DD/MM/YYYY"


Range("A2").Value = "This Report Date: "

Range("B2").Value = myDate

Range("A3").Value = "Next Report Date: "

Range("B3").Value = DateAdd("d", 7, Range("B2").Value)


End Sub


I expect the followiing in column B, because my Region setting is English (United Kingdom)




The result is