Page 179 - Excel Timesaving Techniques for Dummies

34_574272 ch30.qxd 10/1/04 10:50 PM Page 164
30 Creating Efficient
Date and Time

Formulas
Technique

ormulas that calculate elapsed times and dates are right up there
Save Time By after financial formulas in terms of spreadsheet popularity.
F However, unlike financial formulas, which rely on tried-and-true
Checking the computer’s
date and time monetary calculations whose functioning is pretty clear to most business
folks, the way electronic spreadsheet programs deal with dates and
Creating effective date times, especially when used in arithmetic computations, is an almost
formulas complete mystery, right up there with where that matching sock disap-
Creating formulas that pears to in the wash.
compute elapsed times
This technique attempts to make clear how Excel sees dates and times so
that you can efficiently create formulas that calculate elapsed dates and
times. (Where the sock goes is anybody’s guess?) Judging from the
reader mail I get on dates and times in Excel from my other Excel books,
most of you will be happy to just have this one mystery solved!

The Deal with Dates and Times

I must admit that dates and times in an Excel spreadsheet are particu-
larly deceptive. This is because the program is so adept at interpreting
almost any entry that even remotely resembles a date or time as a bona
fide date and time value. As a result, you don’t think twice about dates or
times until you try to use them in simple subtraction formulas that com-
pute how much time has elapsed between them. Then, all of a sudden, it
seems as though Excel doesn’t know jack about dates and times, when, in
fact, it is you who lack the necessary understanding.

Figure 30-1 shows a list of date and time entries in column B and C of a
sample worksheet. Column B shows you how Excel automatically formats
and displays the dates and times that I entered in cells B2:B7. (The date
and time in cell B2 is entered via the NOW function, and the rest were
typed in more or less as they appear.) Column C shows you the actual
values that Excel squirrels away when you make these kinds of date and
time entries. (I revealed their true, arithmetical nature by applying the