Displaying a Two-Digit Year
Q: How can I force Excel to display a two-digit year (instead of a four-digit year) by default?
A: Many of the default settings in Excel, including the date format, are governed by the default settings in the Windows operating system, as pictured in the screen below.
These default settings can be modified as follows:
1. In Windows XP, launch Control Panel and double-click the Regional and Language Options icon, then click the Customize button to open the Customize Regional Options dialog box. On the Date tab, click the Short date format: dropdown arrow in the Short date group and select a two-digit date format. Click OK, OK to complete the process.
2. In Windows Vista, launch Control Panel and select System and Maintenance. Next, from the left-hand menu select Clock, Language, and Region, then Regional and Language Options, then click the Customize this format button to open the Customize Regional Options dialog box. On the Date tab, click the Short date: dropdown arrow in the Date formats group and select a two-digit date format. Click OK, OK to complete the process.
3. In Windows 7, launch Control Panel and select System and Security. Next, from the left-hand menu, select Clock, Language, and Region, then Region and Language, then click the Additional settings button to open the Customize Format dialog box. On the Date tab, click the Short date: dropdown arrow in the Date formats group and select a two-digit date format. Click OK, OK to complete the process.
Note: You must close and restart Excel for any changes to take effect. Thereafter, Excel will display the two-digit date format you specified as the default.
Cautionary point: It is important to understand that even when you are displaying two-digit years, you should still make it a habit to enter four-digit years, otherwise Excel may assume the wrong century. This is because Excel interprets two-digit years ending in 00 through 29 as years 2000 through 2029. (For example, if you type the date 6/2/20, Excel assumes the date is June 2, 2020.) However, Excel interprets two-digit years ending in 30 through 99 as years 1930 through 1999. (For example, if you type the date 6/2/60, Excel assumes the date is June 2, 1960.) You can change the way Excel interprets two-digit years by adjusting the Calendar setting located at the bottom of the Customize Regional Options dialog box (shown in the left-hand column).
More from the JofA:
