CODEWITHSUNDEEP
excelformattingcsvtimestamp
Jon
Jon

Reputation:

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?

Upvotes: 152

Views: 180151

Answers (12)

John Y
John Y

Reputation: 14559

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 two or three pieces: a date portion, a time portion to the second, and optionally a fractional seconds portion. 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 it as a big number (internally, Excel is just using the number of days, including fractional days, since an "epoch" date).

[1]That is, information stored numerically and suitable for use in calculations. If you simply want to visually see as much information as possible, or only need it for sorting/filtering (no date or time arithmetic), you could make up some format which Excel will definitely parse as a string, and thus leave alone; e.g. "yyyymmdd.hhmmss.000".

Upvotes: 27

David Taylor
David Taylor

Reputation: 23

I wrote my timestamps to the CSV file as yyyy-mm-dd hh:mm:ss.

In Excel 365 I create a blank workbook, go to the Data tab and choose "From Text/CSV.

A dialog opens with a preview, which weirdly leaves the first column of timestamps alone, but shows it is going to convert the second column.

You can either "Load" the data, or "Transform Data", which is what I choose.

Now you're in the "Power Query Editor" and you can massage how Excel will bring the columns in.

In my case I undo any automatic changes it made, and tell it both columns are Data Type "Date/Time" using a little button on the ribbon.

Pressing Close & Load brings it into Excel as a data source that you can sort etc.

Upvotes: 0

user13608643
user13608643

Reputation: 1

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.

Upvotes: -1

Brian Rice
Brian Rice

Reputation: 3257

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)

Upvotes: 3

to StackOverflow
to StackOverflow

Reputation: 124804

For second accuracy, yyyy-MM-dd HH:mm:ss should do the trick.

I believe Excel is not very good with fractions of a second (loses them when interacting with COM object IIRC).

Upvotes: 131

user662894
user662894

Reputation: 151

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

Upvotes: 15

Hansjp
Hansjp

Reputation: 66

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

Upvotes: 3

Anoop Velluva
Anoop Velluva

Reputation: 329

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;
}

Upvotes: 0

John Smith
John Smith

Reputation: 41

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.

Upvotes: 4

whardier
whardier

Reputation: 715

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.

Upvotes: 1

Fredrik Mörk
Fredrik Mörk

Reputation: 158399

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

Upvotes: -3

Peter Perháč
Peter Perháč

Reputation: 20792

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

Upvotes: 4

Related Questions