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.
 
Search JabSto ::




Custom Search