Excel 2013 isn’t set up to automatically recognize European date formats in which the number of the day precedes the number of the month and year. For example, you may want 6/11/2014 to represent November 6, 1969, rather than June 11, 2014.
If you’re working with a spreadsheet that uses this type of European date system, you have to customize the Windows Regional settings for the United States so that the Short Date format in Windows programs, such as Excel and Word 2013, use the D/m/yyyy (day, month, year) format rather than the default M/d/yyyy (month, day, year) format.
You can do this by following these steps:
Open the Windows Control Panel in Category View.
To do this in Windows 8, from the Start screen, type con and then click the Control Panel item in the Search Results screen. To do this in Windows 7, you click the Start button on the Windows 7 taskbar followed by Control Panel on the Start menu.
Click the Clock, Language, and Region link in the Control Panel window.
The Control Panel window normally opens in Category view. If it is in Classic view, switch to Category view by choosing Category from the View By drop-down menu.
Click the Region link in the Windows 8 Control Panel window or the Regional and Language Options link in the Windows 7 Control Panel window.
The Region dialog box opens in Windows 8. The Regional and Language Options dialog box opens in Windows 7. In both dialog boxes, the Formats tab is selected.
Click the Additional Settings button.
The Customize Format dialog box opens.
Click the Date tab in the Customize Format dialog box. Click the Short Date format and then type D/m/yyyy, the new date format.
You have to type this European date format because the United States regional settings don’t automatically include this format in the Short Date Style drop-down list. After manually entering this format, the European date format becomes part of the list that you can then select from in the future.
Click OK twice, once to close the Customize Format dialog box and then a second time to close the Region or Regional and Language Options dialog box.
Click the Close button in the upper-right corner of the Control Panel window or press Alt+F4 to close this window.
After changing the Short Date format in the Windows Control Panel, the next time you launch Excel 2013 it automatically interprets short dates using the D/m/yyyy format; so that, for example, 3/5/14 is May 3, 2014, rather than March 5, 2014.