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. (But 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 new 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 initially opening the CSV (before saving), 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?

Someone told me that Excel uses Windows regional date/Time settings to determine how to display timestamps when importing CSV files. But regional settings in Windows have both Long and Short date formats. From Control Panel "Region and Language" -> Formats tab:

Short date: M/d/yyyy
Long date: 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

AFAIK, not by opening them as CSV files. You might be able to do so by importing them as text files, but that would be a lot more work than reformatting a single column. In the long run, you might do better to use a macro to both open the files & reformat the columns in one go.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]