Displaying a Two-Digit Year

BY J. CARLTON COLLINS

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:

 

 Find us on Facebook  |   Follow us on Twitter  |   View JofA videos

SPONSORED REPORT

Time to prepare for overtime changes

As an employer, trusted business adviser, or HR professional, you will need to be aware of exemption guidance, record requirements, advice for clients, and typical problems in applying overtime pay.

QUIZ

News quiz: Good news on pay and benefits for accountants

CPAs can find much to like in recent reports, including news that their expertise and skills are in such demand that pay is expected to rise and that their employers value professional certifications.

CHECKLIST

Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.