[excel] Best timestamp format for CSV/Excel?

I'm writing a CSV file. I need to write timestamps that are accurate at least to the second, and preferably to the millisecond. What's the best format for timestamps in a CSV file such that they can be parsed accurately and unambiguously by Excel with minimal user intervention?

So, weirdly excel imports a csv date in different ways. And, displays them differently depending on the format used in the csv file. Unfortunately the ISO 8061 format comes in as a string. Which prevents you from possibly reformatting the date yourself.

All the ones the do come in as a date... contain the entire information... but they format differently... if you don't like it you can choose a new format for the column in excel and it will work. (Note: you can tell it came in as a valid date/time as it will right justify... if it comes in as a string it will left justify)

Here are formats I tested:

"yyyy-MM-dd" shows up as a date of course when opened in excel. (also "MM/dd/yyyy" works)

"yyyy-MM-dd HH:mm:ss" default display format is "MM/dd/yyyy HH:mm" (date and time w/out seconds)

"yyyy-MM-dd HH:mm:ss.fff" default display format is "HH:mm:ss" (time only w/ seconds)

I believe if you used the double data type, the re-calculation in Excel would work just fine.

I would guess that ISO-format is a good idea. (Wikipedia article, also with time info)

Go to the language settings in the Control Panel, then Format Options, select a locale and see the actual date format for the chosen locale used by Windows by default. Yes, that timestamp format is locale-sensitive. Excel uses those formats when parsing CSV.

Even further, if the locale uses characters beyond ASCII, you'll have to emit CSV in the corresponding pre-Unicode Windows "ANSI" codepage, e.g. CP1251. Excel won't accept UTF-8.

Try MM/dd/yyyy hh:mm:ss a format.

Java code to create XML file.

xmlResponse.append("mydate>").append(this.formatDate(resultSet.getTimestamp("date"), "MM/dd/yyyy hh:mm:ss a")).append("");

public String formatDate(Date date, String format)
{
    String dateString = "";
    if(null != date)
    {
        SimpleDateFormat dateFormat = new SimpleDateFormat(format);
        dateString = dateFormat.format(date);
    }
    return dateString;
}

"yyyy-MM-dd hh:mm:ss.000" format does not work in all locales. For some (at least Danish) "yyyy-MM-dd hh:mm:ss,000" will work better.

"yyyy-mm-dd hh:mm:ss.000" format does not work in all locales. For some (at least Danish) "yyyy-mm-dd hh:mm:ss,000" will work better.

as replied by user662894.

I want to add: Don't try to get the microseconds from, say, SQL Server's datetime2 datatype: Excel can't handle more than 3 fractional seconds (i.e. milliseconds).

So "yyyy-mm-dd hh:mm:ss.000000" won't work, and when Excel is fed this kind of string (from the CSV file), it will perform rounding rather than truncation.

This may be fine except when microsecond precision matters, in which case you are better off by NOT triggering an automatic datatype recognition but just keep the string as string...

The earlier suggestion to use "yyyy-MM-dd HH:mm:ss" is fine, though I believe Excel has much finer time resolution than that. I find this post rather credible (follow the thread and you'll see lots of arithmetic and experimenting with Excel), and if it's correct, you'll have your milliseconds. You can just tack on decimal places at the end, i.e. "yyyy-mm-dd hh:mm:ss.000".

You should be aware that Excel may not necessarily format the data (without human intervention) in such a way that you will see all of that precision. On my computer at work, when I set up a CSV with "yyyy-mm-dd hh:mm:ss.000" data (by hand using Notepad), I get "mm:ss.0" in the cell and "m/d/yyyy  hh:mm:ss AM/PM" in the formula bar.

For maximum information[1] conveyed in the cells without human intervention, you may want to split up your timestamp into a date portion and a time portion, with the time portion only to the second. It looks to me like Excel wants to give you at most three visible "levels" (where fractions of a second are their own level) in any given cell, and you want seven: years, months, days, hours, minutes, seconds, and fractions of a second.

Or, if you don't need the timestamp to be human-readable but you want it to be as accurate as possible, you might prefer just to store a big number (internally, Excel is just using the number of days, including fractional days, since an "epoch" date).

[1]That is, numeric information. If you want to see as much information as possible but don't care about doing calculations with it, you could make up some format which Excel will definitely parse as a string, and thus leave alone; e.g. "yyyymmdd.hhmmss.000".

Given a csv file with a datetime column in this format: yyyy-mm-dd hh:mm:ss

Excel shows it in this format: dd/mm/yyyy hh:mm

e.g. 2020-05-22 16:40:55 shows as 22/05/2020 16:40

This is evidently determined by the Short date and Short time format selected in Windows; for example, if I change the Short date format in Windows to yyyy-mm-dd, Excel shows 2020-05-22 16:40.

Annoyingly, I can't find any way to make Excel show the seconds automatically (I have to manually format the column in Excel). But if the csv file includes a time column in hh:mm:ss format (e.g. 16:40:55), that's what shows in Excel, including the seconds.

As for timezones. I have to store the UTC offset as seconds from UTC that way formulas in Excel/OpenOffice can eventually localize datetimes. I found this to be easier than storing any number that has a 0 in front of it. -0900 didn't parse well in any spreadsheet system and importing it was nearly impossible to train people to do.