Differences between date formatting in VBA-CodePudding

I'd like to achieve the result seen in A1. How come line 3 doesn't output the same result as line 1 & 2 even though the format is the same - yyyy-mm-dd hh:mm:ss?

Does this have anything to do with regional settings?

1   Range("A1").Value2 = Now
2   Range("A1").NumberFormat = "yyyy-mm-dd hh:mm:ss"
3   Range("B1").Value2 = Format(Now, "yyyy-mm-dd hh:mm:ss")

CodePudding user response：

That is because:

A1: You set a Date value and apply a specific format for display.

B1: You format a Date value to Text, set that as the value, but the cell has been applied no specific format, thus Excel sees the text date as a date value and casts it to a true Date value, which is displayed with your default (German?) date format.

To force the text date to be read as text, you could prefix it with a quote:

Range("A1").Value2 = Now
Range("A1").NumberFormat = "yyyy-mm-dd hh:mm:ss"
Range("B1").Value2 = Format(Now, "yyyy-mm-dd hh:mm:ss")
Range("C1").Value2 = Format(Now, "\'yyyy-mm-dd hh:mm:ss")

Output (Danish localisation):

Note, that C1 is text (left-aligned).

CodePudding user response：

Why you not set, before or after, your cell formatting with a Range?

Example:

Range("A1:Z1").NumberFormat = "yyyy-mm-dd hh:mm:ss"
Range("A1").Value2 = Now
Range("B1").Value2 = Now