Best timestamp format for CSV Excel

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 nbsp  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

[excel] Best timestamp format for CSV/Excel?

Examples related to excel

Examples related to formatting

Examples related to csv

Examples related to timestamp