Wrong format in date

[brianfosterblack]

I am using this line of code in my macro but it gives the wrong date format

    Range("Reports!H3").Value = Format(Now, "dd/mm/yyyy HH:mm:ss")

I am getting the date as mm/dd/yyyy and then hours and seconds
all other dates in the workbook are fine but it gets confusing when most dates are 10/9/2021 and this date is 9/10/2021 because the month is first.

I cannot seem to change this format, even in the cell and it is only when I run code for a date without the time that I get the right format.
I have checked all my regional settings and they are right.

 

[SQUIDD]

try this

Range("Reports!H3").Value = CDate(Now)

 

[brianfosterblack]

That works perfectly. Can you please explain why?
Thank you very much for the help.

 

[SQUIDD]

Hi

Glad that helped, as for why, all i know is i have had so many date format issues with my projects over the years, that when i discovered this, probably by someone on here pointing me there, i always use this now.

i think it literally converts values into date.

i would say, dates in excel have been one of the most frustrating things i have ever had to deal with, especially automtic date recognittion.

maybe someone else has a better explination

but at least its working now

dave