Microsoft Office Tutorials and References

In Depth Information

**It Takes All Types**

The dating game

Dates are stored as serial numbers that

indicate how many days have elapsed from a

particular starting date; times are stored as

decimal fractions indicating the elapsed part

of the 24-hour period. Excel supports two date

systems: the 1900 date system used by Excel in

Windows, where January 1, 1900 is the starting

date (serial number 1) and the 1904 system used

by Excel for the Macintosh, where January 2,

1904 is the starting date.

If you ever get ahold of a workbook created

with Excel for the Macintosh that contains

dates that seem all screwed up when you open

the file, you can rectify this problem by opening

the Advanced tab of the Excel Options dialog

box (File➪Options➪Advanced or Alt+FTA) and

then selecting the Use 1904 Date System check

box in the When Calculating This Workbook

section before you click OK.

Make it a date in the 21st Century

Contrary to what you might think, when entering dates in the 21st Century,

you need to enter only the last two digits of the year. For example, to enter

the date January 6, 2012, in a worksheet, I enter
1/6/12
in the target cell.

Likewise, to put the date February 15, 2013, in a worksheet, I enter
2/15/13
in

the target cell.

Entering only the last two digits of dates in the 21st Century works only for

dates in the first three decades of the new century (2000 through 2029). To

enter dates for the years 2030 on, you need to input all four digits of the year.

This also means, however, that to put in dates in the first three decades

of the 20th Century (1900 through 1929), you must enter all four digits of

the year. For example, to put in the date July 21, 1925, you have to enter

7/21/1925
in the target cell. Otherwise, if you enter just the last two digits

) for the year part of the date, Excel enters a date for the year 2025 and
25

not 1925!

Excel 2013 always displays all four digits of the year in the cell and on the

Formula bar even when you only enter the last two. For example, if you enter

11/06/12
in a cell, Excel automatically displays 11/6/2012 in the worksheet

cell (and on the Formula bar when that cell is current).