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

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.