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
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).