Microsoft Office Tutorials and References

In Depth Information

**Working with Dates: Dates Are Numbers Too**

Working with Dates: Dates Are Numbers Too

Enter the expression 3/4 in a cell and you may be in for a surprise. You know that 3/4

can’t qualify as an actual fraction, because you’ve written it without the equal sign. So

maybe it’s just a bit of text, you may surmise. But it isn’t; Excel will treat is as a date.

Depending on where in the world you live, that expression will be treated as either

March 4 or April 3—but either way, it’s a date. That’s because Excel has decided that

3/4 and expressions like it will be regarded by default as a date, and assumes the date

occurs during the current year—because people often like to write dates that way.

Treating 3/4 as a date is one instance of what’s called Excel’s
general
format, the default

worksheet setting. The general format tries to understand what you have in mind when

you enter data in a cell—
before
you’ve carried out any of the formatting changes

discussed previously. Thus, Excel makes an educated guess about that 3/4, assigning it

the status of a date unless and until you tell it otherwise.

But what you really need to know about dates is that they’re numbers. In fact, March 4,

2011 is actually 40606—but what in the world does that mean? It means this: each date

is assigned a sequenced number denoting the total days separating that date from

January 1, 1900. Thus, March 4, 2011 arrived 40606 days after the baseline January 1,

1900—and that’s a very good thing to know, because now you can determine the

number of days between any two dates.

Here’s an example:

1.

Enter 3/4 in cell A2.

2.

Then enter 1/15 in A3.

3.

In cell A4, write
=A2-A3
, and you answer will be 48.

The 48 represents the number of days between January 15, 2011 and March 4, 2011, a

result made possible because the later date has that numerical value of 40606, and the

January date is really 40558. And 40606 – 40558 = 48.