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

Improve this question

2 Answers 2

Reset to default
2

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"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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%....

Improve this answer

2 Comments

Aha! Now that you suggest that I see this answer stackoverflow.com/a/27388857/645715
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.