Clever Geek Handbook

Excel default date format yyyy-mm-dd hh: mm: ss versus yyyy-mm-dd hh: mm

I am using Excel 2010 or Excel 2007 to import a CSV file with date / time information. The time stamp in my CSV file is in the following format: yyyy-mm-dd hh: mm: ss. (for example: 2015-07-17 12:34:56)

My problem is that Excel seems to automatically detect that the field is a date / time and it removes the second. So it shows my data as "2015-07-17 12:34" without the second. Then, when I save the file to CSV again, the second is discarded and no longer present in the CSV!

I find it very difficult to change the format of the user cells 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 and time format in Excel Preferences or Windows Registry?

What's the best (correct) way to get Excel to save the second when I open and save a CSV file with its / time information.

Thanks, ssinfod

+3


source to share


3 answers


It totally depends on your system date and time format:



  • Go to Control Panel,
  • Click "Region and Language"
  • Click "Change date, time and number format"
  • Set "short date", "long date", "short time" and "long time".
+3


source


in 'format Cells → custom' write

yyyy-mm-dd h:mm:ss;@


in the Type field

+2


source


If you open the CSV file, the seconds will not be discarded, they will be hidden by default:

enter image description here

If double-clicking would result in:

enter image description here

If you save Excel as CSV then release ... To save those seconds, you have to reformat the cells like this:

yyyy-mm-dd hh: mm: ss

before executing SaveAs

+1


source






More articles:


All Articles