Microsoft Office Tutorials and References
In Depth Information
Chapter 12: Dressing Up for Date Functions
Because Excel represents dates in this way, it can work with dates in the
same manner as numbers. For example, you can subtract one date from
another to find out how many days are between them. Likewise, you can
add 14 to today’s date to get a date two weeks in the future. This trick is
very useful, but people are used to seeing dates represented in traditional
formats, not as numbers. Fortunately, Excel uses date serial numbers only
behind the scenes, and what you see in your workbook are dates in the
standard date formats such as Jan 20, 2013, and 12/20/13.
In Excel for the Mac, the serial numbering system begins on January 1, 1904.
The way years are handled requires special mention. When a year is fully
displayed in four digits, such as 2005, there is no ambiguity. However, when
a date is written in a shorthand style, such as in 3/1/02, it isn’t clear what the
year is. It could be 2002, or it could be 1902. Say 3/1/02 is a shorthand entry
for someone’s birthday. Then on March 1, 2005, he is either 3 years old or
103 years old. In those countries that write dates as dd/mm/yy, this would be
January 3, 1902, or January 3, 2002.
Excel and the Windows operating system have a default way of interpreting
shorthand years. Windows has a setting in the Customize Regional Options
dialog box found in the Control Panel. This setting guides how Excel will
interpret years. If the setting is 1930 through 2029, then 3/1/02 indicates the year
2002, but 3/1/45 indicates the year 1945, not 2045. Figure 12-1 shows this setting.
Here’s how to open and set it:
The following instructions may differ depending on which version of Windows
you have running on your computer.
1. Click your computer’s Start button.
2. Select Control Panel.
3. Select Clock, Language and Region.
4. Select the Region and Language.
The Region and Language dialog box opens.
5. Click the Formats tab.
6. Click the Additional settings button.
The Customize Format dialog box opens.
7. Click the Date tab.
8. Enter a four-digit ending year (such as 2029) to indicate the latest year
that will be used when interpreting a two-digit year.