Dates are often a vital part the data analysis. You frequently ask concerns such as: once was a product purchased, how long will a job in a project take, or what is the average revenue for a fiscal quarter? Entering days correctly is important to ensuring precise results. But formatting dates so the they are easy to understand is equally vital to ensuring correct interpretation of those results.

You are watching: When you enter a two-digit year that is less than 30, excel changes the year to


Important: Because the rules that govern the way that any kind of calculation regime interprets days are complex, you must be as details as feasible about dates whenever you get in them. This will develop the highest level that accuracy in your day calculations.


Excel stores dates as sequential numbers the are called serial values. Because that example, in Excel because that Windows, January 1, 1900 is serial number 1, and also January 1, 2008 is serial number 39448 because it is 39,448 job after January 1, 1900.

Excel shop times together decimal fractions because time is considered a section of a day. The decimal number is a value ranging from 0 (zero) come 0.99999999, representing the time from 0:00:00 (12:00:00 A.M.) come 23:59:59 (11:59:59 P.M.).

Because dates and also times are values, they deserve to be added, subtracted, and also included in various other calculations. You have the right to view a day as a serial value and also a time together a decimal portion by transforming the layout of the cabinet that contains the day or time to general format.


Learn around the two day systems

Both Excel because that Mac and also Excel for windows smashville247.net the 1900 and also 1904 date systems. The default day system for Excel for home windows is 1900; and also the default date system because that Excel for Mac is 1904.

Originally, Excel for home windows was based on the 1900 day system, due to the fact that it enabled better compatibility with other spreadsheet programs that were design to run under MS-DOS and also smashville247.net Windows, and also therefore it ended up being the default date system. Originally, Excel because that Mac was based upon the 1904 date system, since it enabled better compatibility with early on Macintosh computer systems that did no smashville247.net dates before January 2, 1904, and also therefore it ended up being the default date system.

The following table mirrors the an initial date and the last date for each date system and also the serial value linked with every date.

day system

First date

Last date

1900

January 1, 1900(serial worth 1)

December 31, 9999(serial worth 2958465)

1904

January 2, 1904(serial value 1)

December 31, 9999(serial value 2957003)

Because the two date systems use different beginning days, the same date is represented by various serial worths in each date system. For example, July 5, 2007 deserve to have two various serial values, relying on the date system the is used.

date system

Serial worth of July 5, 2007

1900

37806

1904

39268

The difference in between the two day systems is 1,462 days; that is, the serial value of a day in the 1900 date system is always 1,462 days better than the serial value of the same day in the 1904 date system. Conversely, the serial worth of a date in the 1904 date system is constantly 1,462 days much less than the serial value of the same date in the 1900 day system. 1,462 work is equal to four years and one work (which contains one leap day).


In smashville247.net Windows, girlfriend can readjust the way two-digit year are construed for all windows programs the you have installed.

Windows 10

In the search box on the taskbar, kind control panel, and also then select Control Panel.

Under Clock, Language and also Region, click Change date, time, or number formats

Click Regional and also Language Options.

In the Region dialog box, click Additional settings.

Click the Date tab.

In the When a two-digit year is entered, interpret it together a year between box, adjust the upper limit because that the century.

As you readjust the upper-limit year, the lower-limit year automatically changes.

Click OK.

Windows 8

Swipe in from the right edge that the screen, insanity Search (or if you"re utilizing a mouse, suggest to the upper-right edge of the screen, move the mouse pointer down, and then click Search), enter Control Panel in the find box, and also then madness or click Control Panel.

Under Clock, Language and also Region, click Change date, time, or number formats.

In the Region dialog box, click Additional settings.

Click the Date tab.

In the When a two-digit year is entered, interpret it as a year between box, readjust the top limit because that the century.

As you readjust the upper-limit year, the lower-limit year immediately changes.

Click OK.

Windows 7

Click the Start button, and also then click Control Panel.

Click Region and also Language.

In the Region dialog box, click Additional settings.

Click the Date tab.

In the When a two-digit year is entered, analyze it as a year between box, readjust the upper limit because that the century.

As you readjust the upper-limit year, the lower-limit year immediately changes.

Click OK.


By default, as you enter days in a workbook, the days are formatted to screen two-digit years. Once you readjust the default date format to a various format by using this procedure, the screen of days that to be previously gone into in her workbook will adjust to the new format as long as the days haven"t been formatted by utilizing the format Cells dialog crate (On the Home tab, in the Number group, click the Dialog box Launcher).

Windows 10

In the search box ~ above the taskbar, form control panel, and then choose Control Panel.

Under Clock, Language and also Region, click Change date, time, or number formats

Click Regional and also Language Options.

In the Region dialog box, click Additional settings.

Click the Date tab.

In the Short day format list, click a style that uses four digits because that the year ("yyyy").

Click OK.

Windows 8

Swipe in native the best edge of the screen, insanity Search (or if you"re utilizing a mouse, allude to the upper-right edge of the screen, relocate the mouse pointer down, and then click Search), enter Control Panel in the search box, and also then madness or click Control Panel.

Under Clock, Language and Region, click Change date, time, or number formats.

In the Region dialog box, click Additional settings.

Click the Date tab.

In the Short day format list, click a layout that uses four digits because that the year ("yyyy").

Click OK.

Windows 7

Click the Start button, and then click Control Panel.

Click Region and also Language.

In the Region dialog box, click Additional settings.

Click the Date tab.

In the Short date format list, click a style that uses four digits for the year ("yyyy").

Click OK.


You can encounter difficulties when girlfriend copy and paste dates or as soon as you produce external references between workbooks based upon the 2 different date systems. Days can show up four years and one day earlier or later on than the date that you expect. You have the right to encounter these problems whether you space using Excel for Windows, Excel for Mac, or both.

For example, if you copy the date July 5, 2007 from a workbook that provides the 1900 date system and also then paste the date into a workbook that supplies the 1904 date system, the date shows up as July 6, 2011, i beg your pardon is 1,462 job later. Alternatively, if girlfriend copy the date July 5, 2007 indigenous a workbook that provides the 1904 date system and also then dough the date into a workbook that uses the 1900 date system, the date appears as July 4, 2003, which is 1,462 job earlier. For background information, see day systems in Excel.

correct a copy and paste problem

In an north cell, enter the value 1462.

Select that cell, and also then top top the Home tab, in the Clipboard group, click Copy.

On the Home tab, in the Clipboard group, click Paste, and also then click Paste Special.

*

In the Paste Special dialog box, under Paste, click Values, and then under Operation, do one of the following:

To set the date as four years and one work later, click Add.

To set the date as 4 years and also one day earlier, click Subtract.

See more: Used 1997 Jeep Grand Cherokee Bolt Pattern On A 97 Zj, Used 1997 Jeep Grand Cherokee Rims

Correct an exterior reference problem

If you are using an outside reference come a day in another workbook v a different date system, you have the right to modify the external reference by doing one of the following:

To collection the day as four years and one day later, include 1,462 come it. For example:

=Sheet1!$A$1+1462

To set the day as four years and one day earlier, subtract 1,462 indigenous it. Because that example:

=Sheet1!$A$1-1462


Need an ext help?

You can always ask an professional in the Excel technology Community or get smashville247.net in the answers community.