Results 1 to 3 of 3

Thread: Default date/time format when importing CSV files

  1. #1

    Default date/time format when importing CSV files



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 r
    egional 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,
    eb


  2. #2
    Wow, I stumped all the Excel experts! Posted this on a number of forums, and no one has been able to provide an answer.

  3. #3
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    Hi eb,

    Not sure if this is the issue or not, (yuo'll need to test it yourself), but try changing your short time format. Currently you are using "h:mm tt" which does not include seconds (the "tt" only activates the AM/PM choice). Try using "hh:mm:ss" or "HH:mm:ss" instead.

    Secondly, on the point of posting to several forums, you may wish to avoid cross-posting.

    Cheers,

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •