Differences between date formatting in VBA

Question

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")

I don't have the why (I'm guessing excel is trying to be smart for you) but if you have date data, please keep it as date and modify the presentation through NumberFormat — Raymond Wu
– Raymond Wu, Commented Nov 29, 2021 at 16:58

Gustav · Accepted Answer · 2021-11-29 17:56:50Z

1

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).

answered Nov 29, 2021 at 17:56

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rayearth Over a year ago

I see. This answers the question. Thank you @Gustav

Berzohr · Accepted Answer · 2021-11-29 17:03:01Z

0

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

answered Nov 29, 2021 at 17:03

Berzohr

4861 gold badge6 silver badges21 bronze badges

Collectives™ on Stack Overflow

Differences between date formatting in VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related