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

No responses? So there's no way to do this?

Can you attach a sample csv?

The forum software won't let me upload a ".csv" file, so I have attached it as a ".txt" file.

Also attached is a screenshot of the way it looks when I open it in Excel.

The timestamps contain hh:mm:ss, but the seconds don't show. If I save this back out to csv, it will truncate the seconds.

Attached Images

sample.png (19.9 KB, 13 views)

Attached Files

sample.txt (849 Bytes, 8 views)