View Single Post
 
Old 09-19-2014, 10:51 AM
hwg hwg is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Feb 2014
Posts: 8
hwg is on a distinguished road
Default Default date, time format when importing CSV files

I often use Excel to view CSV files that contain timestamps. When I open the CSV files in excel, the timstamps get formatted to MM/DD/YY hh:mm

Seconds are not displayed. They are still there. If I edit a timestamp cell I can see the full timestamp, with seconds. But if I then save this file back to CSV, the seconds are truncated, and the CSV only contains the hour and minute of the timestamp, with all seconds showing 00.

I can reformat all the dates to MM/DD/YY hh:mm:ss after opening the CSV, but that's a pain to do every time. How can I get Excel to use MM/DD/YY hh:mm:ss as the default date/time format when importing CSV files?

Excel gets the date format from the regional settings in Windows. Regional settings in Windows have both Long and Short date formats. From Control Panel "Region and Language" -> Formats tab:

Short gate: M/d/yyyy
Long date: dddd, MMMM dd, yyyy
Short time: h:mm tt
Long time: h:mm:ss tt

How do I tell Excel to use the Long Time format instead of the Short Time, when importing CSV's? Or, better, how do I tell it to use a format I specify?

Thanks,
hwg
Reply With Quote