Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-10-2015, 10:51 AM
hwg hwg is offline Default date/time format when importing CSV files Windows 7 64bit Default date/time format when importing CSV files Office 2010 64bit
Novice
Default date/time format when importing CSV files
 
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. (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
Reply With Quote
  #2  
Old 09-11-2015, 11:16 PM
macropod's Avatar
macropod macropod is offline Default date/time format when importing CSV files Windows 7 64bit Default date/time format when importing CSV files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,369
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by hwg View Post
How can I get Excel to use MM/DD/YY hh:mm:ss as the default date/time format when importing CSV files?
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]
Reply With Quote
Reply

Tags
csv, import, time



Similar Threads
Thread Thread Starter Forum Replies Last Post
Default date format gebobs Excel 0 03-03-2015 10:16 AM
Default date/time format when importing CSV files Default date, time format when importing CSV files hwg Excel 3 11-17-2014 08:47 AM
Default date/time format when importing CSV files Parse date and time from one cell and paste it to another for all files in a folder? chrisd2000 Excel Programming 3 07-03-2014 10:56 AM
How to format cell in order to display *both* time and date SamyCode Excel 3 01-22-2013 03:30 PM
Default date/time format when importing CSV files default font for date and time insert kennystringer Word 2 12-21-2011 12:46 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:19 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft