Microsoft Office Tutorials and References
In Depth Information
Time Is On Your Side—Yes It Is
Look closely at the screen shot and you’ll see that each format presents its proposed “version” of
34567, that is, how the number would look were you to select this or that format. And look in particular
at Short and Long Date.
Understanding this formative concept (it probably qualifies as a have-to-know)—that dates are
really numbers—helps you understand in turn that if you write April 6, 2001 in cell A1 (in any date
format) and July 12, 1983 in cell A2, you can then write:
and realize an answer of 6478, which signifies the number of days between the two dates you’ve
entered—because what you’ve really done here is subtract 30509.00 from 36987.00.
Thus a date format—and again, that’s what it is, a format —masks what’s really a number in date
terms. And so if I write say, 23786 in a cell, select it, and select Short Date, I’ll see:
That’s the mode of date presentation that Excel calls Short Date. Select that same cell and click on
Long Date, and I’ll see:
February 13, 1965
And if write 7/8 in a cell and select Short Date, I’ll see 07/08/2010. Choose Long Date, and July 08,
2010 emerges. Note in this case 7/8 omits the year; and when one does just that, Excel assumes you’re
referring to a date in the current year. But remember what I said earlier. If I type 7/8 under the General
format—without earmarking any date format at all—I’ll still see a date entry in the cell, because even
the General format thinks you meant to type a date anyway. But this is what you’ll see:
an even briefer format than Short Date. It’s a really short date.
To sum up, Date formats paint a chronological veneer over what is really, when the smoke clears,
just a number. And bear in mind that the inverse applies: if I type 7/8 and Jul-08, I can return to that
cell, click the Number format, and see: 40367.00. But why the two decimal points?
Time Is On Your Side—Yes It Is
I was afraid you’d ask that question, and in order to answer it we need to bump down to the next
formatting selection— Time . To Excel, any time or clock reading in a 24–hour span can be treated as a
fraction of a 24–hour denominator. What does that mean? It means this: if I type.346 in a cell, and then
apply the Time format to that cell, I’ll see:
Yeah, I also found this baffling at first—and second—sight. But think about it: the above clock time
actually represents .346 of a day in hourly terms; that is, 8:18:14 is the time of day which stands for
34.6% of an entire 24–hour span. And to trot out perhaps the simplest illustration, type .5 in that cell,
and format it with Time. You’ll see:
Get it? That’ s noon—exactly half, or .5, of a day.
Thus the Date formats provide a default number with two decimal points, e.g., 32456.00, in order to
enable you to format a cell with either date or time readings If you enter 31456.17 in a cell and opt for .
Short Date, you’ll muster 02/13/1986. But if you select the Time format instead, you’ll see 04:04:48, the
time of day which stands for exactly .17 of an entire day. Choose a Date format, and the original