Page 124 - Excel Workbook for Dummies

12_798452 ch07.qxp 3/13/06 7:51 PM Page 107

Chapter 7

Creating Date and

Time Formulas

In This Chapter
Building date and time formulas
Using the Date functions
Using the Time functions

ormulas that perform calculations between elapsed dates and times are quite common
Fin spreadsheets. The exercises in this chapter give you a chance to build formulas that
calculate the difference between starting and ending dates and times as well as to use Excel
Date and Time functions to perform a variety of tasks including returning the current date
and time in a spreadsheet, converting text entries to valid date and time numbers, and
more.

Constructing Date and Time Formulas

You already have some experience entering dates and times in a worksheet and then chang-
ing how they appear by assigning different date and time number formats. Remember that
all you need to do when you need to enter a date or time number in a worksheet is to emu-
late one of these date or time number formats when making the entry.
The only thing the least little bit baffling about date and time entries in a worksheet is the
actual way that Excel stores the date and time numbers into a cell when you make an entry
following one of the standard number formats. Behind the scenes (that is, the date or time
number formats) lies a serial number. In the case of dates, this serial number represents
the number of days that have elapsed between the date you enter and the beginning of the
twentieth century (making January 1, 1900 serial date number 1). In the case of time, the
serial number is a fraction representing the number of hours, minutes, and seconds that
have elapsed since midnight (which is serial number 0.00000000, so that 12 noon is
0.50000000).

To build a simple date formula that calculates the number of days that have elapsed
between two dates entered in the worksheet, subtract the cell containing the starting date
from the one containing the ending date. Excel then displays the calculated result in a date
format, which you then you have to reformat with another number format (such as Number
with no decimal points) to see the number of days that this “date” actually represents.
To build a simple time formula that calculates the fractional part of the day that has elapsed
between two times entered in the worksheet, you subtract the cell containing the starting
time from the one containing the ending time. Excel then displays the calculated result as a
fraction that you can then convert into hours by multiplying by 24.