Microsoft Office Tutorials and References

In Depth Information

**How Excel Identifies Dates and Times**

Adding Different

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

displays 11/7/2009.

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

another.