Menu
I'm using Excel 2010 or Excel 2007 to import CSV file with date/time information. The time stamp in my CSV file have the following format: yyyy-mm-dd hh:mm:ss. (ex: 2015-07-17 12:34:56)
My problem is that Excel seems to auto-detect that the field is a date/time and it is removing the second. Therefore it shows my data as: "2015-07-17 12:34" without the second. Then, when I save my file again in CSV the second are discarded and not present in the CSV anymore !
I find it very time consuming to change the custom cells format of all my files from "yyyy-mm-dd hh:mm" to "yyyy-mm-dd hh:mm:ss".
Is there a way to change the default date/time format in Excel setting or Windows registry?
What is the best (correct) way to force Excel to keep the second when I open and save a CSV file with data/time information in it.
Thanks, ssinfod
493
Excel default date time format yyyy-mm-dd hh:mm:ss versus yyyy-mm-dd hh:mm.
Either use =DATE()+TIME() or write dates in separate cells and use references in your formula like =TEXT(A1-A2,"h:mm") .
On the Home tab, in the Number group, click the Dialog Box Launcher next to Number. You can also press CTRL+1 to open the Format Cells dialog box. In the Category list, click Date or Time. In the Type list, click the date or time format that you want to use.
in 'format Cells -> custom' write
yyyy-mm-dd h:mm:ss;@
in the Type box
176
It is completely depend on your system date and time format:
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
