Microsoft Office Tutorials and References
In Depth Information
How Excel Identifies Dates and Times
Types of Data
• If you don’t type in any year at all, Excel automatically assumes you mean the
current year . For example, when you enter 7/4, Excel inserts the date 7/4/2010
(assuming it’s currently 2010 on your computer’s internal clock). When you
enter a date this way, the year component doesn’t show up in the cell, but it’s still
stored in the worksheet (and visible on the formula bar).
• Excel understands and displays dates differently depending on the regional
settings on your computer . Windows has a setting that determines how your
computer interprets dates (see the next section, page 395). On a computer that’s
configured with U.S settings, Month-Day-Year is the standard progression. But
on a UK-configured computer, Day-Month-Year is the deal. For example, in the
U.S., either 11-7-09 or 11/7/09 is shorthand for November 7, 2009. In the UK or
in Canada, the same notations refer to July 11, 2009.
Thus, if your computer has U.S. regional settings turned on, and you type in
11/7/09 , then Excel understands it as November 7, 2009, and the formula bar
Note: The way Excel recognizes and displays dates varies according to the regional settings on your
computer, but the way Excel stores dates does not. This feature comes in handy when you save a worksheet
on one computer and then open it on another computer with different regional settings. Because Excel
stores every date the same way, the date information remains accurate on the new computer, and Excel
can display it according to the current regional settings.
Typing in times is more straightforward than typing in dates. You simply use
numbers, separated by a colon (:). You need to include an hour and minute component
at minimum (as in 7:30), but you can also add seconds (as in 7:30:10). You can use
values from 1 to 24 for the hour part, though if your system’s set to use a 12-hour
clock, Excel converts the time accordingly (in other words, 19:30 becomes 7:30 PM).
If you want to use the 12-hour clock when you type in a time, follow your time with
a space and the letters P or PM (or A or AM).
Finally, you can create cells that have both date and time information. To do so, just
type the date portion first, followed by a space, and then the time portion. For
example, Excel happily accepts this combo: 7/4/2010 1:30 PM.
Behind the scenes, Excel stores dates as serial numbers . It considers the date January
1, 1900 to be day 1. January 2, 1900 is day 2, and so on, up through the year 9999.
This system is quite nifty because if you use Excel to subtract one date from another,
then you actually end up calculating the difference in days, which is exactly what you
want. On the other hand, it means you can’t enter a date in Excel that’s earlier than
January 1, 1900 (if you do, Excel treats your date like text).
Similarly, Excel stores times as fractional numbers from 0 to 1. The number 0
represents 12:00 a.m. (the start of the day) and 0.99999 represents 11:59:59 p.m. (the end
of the day). As with dates, this system allows you to subtract one time value from