Set a String Value in a cell adn avoid it missinterpreted as a US date

Question

I have a code as simple as:

Cells(1,1).Value="10/02/2018"

and I want cell A1 to have a value of 10/02/2018 either as Text or Date, but Excel is stupid enough to write 02/10/2018 in cell A1.

Besides cursing Excel programmers, how can I fix this?

Thanks

Set the cell format to text first (`Cells(1,1).NumberFormat = "@"`), or set the cell format to the desired date format that will result in what you're looking for. If that's October 2, 2018, the format would be `mm/dd/yyyy` but if it's 10th of February, 2018, the format would be `dd/mm/yyyy` — tigeravatar, Oct 09 '18 at 13:19
Possible duplicate of [How to change Format of a Cell to Text using VBA](https://stackoverflow.com/questions/8265350/how-to-change-format-of-a-cell-to-text-using-vba) — Imran Malek, Oct 09 '18 at 13:21
Alternately, you could put an apostrophe at the beginning of the cell contents and that will also have Excel treat it like text: `Cells(1, 1).Value = "'10/02/2018"` — tigeravatar, Oct 09 '18 at 13:25
Just a note: Saving a date as text in the suggested format is the **worst** of all ideas. Because if you do that neither the computer nor a human can say if the date is actually `MM/DD/YYYY` or `DD/MM/YYYY`. Happy guessing then :) Since globalization the only recommendable text format can be `YYYY-MM-DD` ([ISO 8601](https://en.wikipedia.org/wiki/ISO_8601)). — Pᴇʜ, Oct 09 '18 at 13:32

score 2 · Answer 1 · answered Oct 09 '18 at 13:19

So which date format do you want? MM/DD/YYYY or DD/MM/YYYY?

You can change it by using

Cells(1, 1).NumberFormat = "DD/MM/YYYY"

And you can write a proper date like

Cells(1, 1).Value = DateSerial(2018, 2, 10) 'DateSerial(Year, Month, Day)

Nevertheless I highly recommend to use the ISO 8601 date format YYYY-MM-DD which is the only one that cannot be misinterpreted by humans.

score 1 · Answer 2 · answered Oct 09 '18 at 13:19

If you want a date, format the cell as 'mm/dd/yyyy' after inserting the value:

Cells(1,1).Value="10/02/2018"
cells(1,1).NumberFormat = "mm/dd/yyyy"

If you want it as Text then format the cell as text before inserting the value:

cells(1,1).NumberFormat = "@"
Cells(1,1).Value="10/02/2018"

2 Answers2