CODEWITHSUNDEEP
excelcsvdate-formatting
GlenPeterson
GlenPeterson

Reputation: 5216

What date format can you use in CSV that Excel will recognize unambiguously?

Surely someone before me has needed to produce a year, month, day in a single field for a CSV that "just works" in popular versions of Microsoft Excel? I want only a date, no timestamp, though I suppose I could include 00:00 or something like that if I absolutely had to.

Panagiotis Kanavos points out that "Excel can only import it and try to guess whether the text values correspond to a certain type, using the user's locale settings." My question is about what format will cause Excel to guess correctly in the US and Europe, and ideally everywhere else.

If it's impossible or unreliable to do this in CSV, I will accept a link to using some zipped XML format or something that Excel and other spreadsheets accept universally instead of CSV.

This is NOT a duplicate of of the following:

Upvotes: 1

Views: 4042

Answers (2)

Gary's Student
Gary's Student

Reputation: 96791

The difficulty with answering your question is that to test the proposed answer, the format must be tested in "all popular versions" of Excel

I have several versions of Excel and in my testing this:

enter image description here

worked in all my versions (English-US Locale)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Excel will recognize YYYY-MM-DD as a global standard.

enter image description here

Cartoon from: https://www.xkcd.com/1179/

Upvotes: 4

Related Questions