Excel 2016 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.
When running Excel 2016 on Windows 10, you follow these steps:
Click the Windows Start button and then click Settings on the Start menu.
Windows 10 opens the Settings dialog box.
Click the Time & Language button in the Settings dialog box.
The Date and Time settings appear in the Settings dialog box.
Click the Change Date and Time formats link that appears under the Format examples that show you the current long and short date and time formatting.
The Settings dialog box displays drop-down text boxes where you can select new formatting for the short and long dates.
Click the Short Date drop-down button, click the dd-MMM-yy format at the bottom of the drop-down list, and then click the Close button.
If you're running Excel 2016 on Windows 7 or 8, you 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.
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 Clock, Language, and Region link in the Control Panel window.
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 10 Settings dialog box or the Windows 7 or 8 Control Panel, the next time you launch Excel 2016, it automatically formats dates à la European; so that, for example, 3/5/16 is interpreted as May 3, 2016, rather than March 5, 2016.
Don't forget to change the Short Date format back to its original M/d/yyyy Short Date format for your version of Windows when working with spreadsheets that follow the "month-day-year" Short Date format preferred in the United States. Also, don't forget that you have to restart Excel to get it to pick up on the changes that you make to any of the Windows date and time format settings.