CODEWITHSUNDEEP
excelvba
Repairer
Repairer

Reputation: 135

Excel showing wrong date time format

I am using this below code

Range("A1") = Format$(Now, "dd/mm/yyyy h:mm:ss AM/PM")

and the o/p as shown in A1 is 02/12/2020 20:08:38 which is incorrect as today is 12th Feb 2020, so it should be 12/02/2020 20:08:38. Just the dd is interchanged with mm and there is no AM/PM. Already have checked the control panel--region and it's English (United Kingdom). OS is Win10. My system shows 12/02/2020 as the date. When I write 12/02/2020 manually it accepts as it is and doesn't change it. Don't have any clue about it. Thanks for any help.

Upvotes: 0

Views: 625

Answers (1)

BigBen
BigBen

Reputation: 50162

Instead of using Format$ (which returns a String anyway, not an actual datetime), change the NumberFormat of the cell:

Range("A1").NumberFormat = "dd/mm/yyyy h:mm:ss AM/PM"
Range("A1").Value = Now

Upvotes: 1

Related Questions