3

Is there a way to set a default date format in Excel using four-digit years?

Our process receives CSV files generated by an external process (e.g. SAS, SPSS) and read into another application. Sometimes it's necessary to open, edit and save the CSV, for instance to delete columns of sensitive data, correct an outlier, etc.

The challenge is that Excel reads valid dates in the CSV (e.g. 8/23/2013), converts them to dates and displays them with two digit years. So when you save it, the dates are stored as two digit years, thus Inflicting the Y2K problem on ourselves.

I know it's not possible to get Excel to stop recognizing dates (at least not without changing the CSV file or laboriously specifying format for each column using the Text Import wizard). But maybe get its default date format to use four digit years?

Stop Excel from automatically converting certain text values to dates

Community
  • 1
  • 1
prototype
  • 7,249
  • 15
  • 60
  • 94

2 Answers2

1

On OSX, in Excel

  • select "Excel" from the top menu,
  • select "Preferences"
  • selected "Edit"
  • select "Preserve display of dates entered with four digit years"

On Windows

  • From Windows Start menu, click Control Panel.
  • Double click the Regional and Language Options icon or folder.
  • In the Regional and Language Options dialog box, click the Regional Options tab.
  • Click Customize.
  • Click the Date tab.
  • In the Short date format list, click a format that uses four digits for the year ("yyyy"
prototype
  • 7,249
  • 15
  • 60
  • 94
1

Follow the below steps to solve this issue.

Open Control Panel Select Region Select Additional Settings Goto Time Tab In Time Format : Short Time Type : change the format to hh.mm tt (Don't use : , You should use dot . only for hours minutes separation) Long Time Type : change the format to HH.mm.ss Apply the changes. Restart the Excel. Now Excel will not automatically convert to date format in csv files.

This will work 100%....

Ganesh
  • 11
  • 3
  • Aha! Now that you suggest that I see this answer https://stackoverflow.com/a/27388857/645715 – prototype Sep 23 '20 at 01:32
  • Why am I suspicious when someone says "xyz will work 100%"? Oh yeh - that's because it almost 100% never works! As was the case here. – hazymat Jul 04 '23 at 20:02