Microsoft Office Tutorials and References

In Depth Information

**Working with date and time functions**

format (one that doesn’t include AM/PM) to display
14:23
. Excel displays the result relative

to midnight.

Suppose you want to determine the time that is 2 hours, 23 minutes, and 17 seconds after

12:35:23 PM. The formula =("12:35:23 PM")+("2:23:17") returns the correct answer,
.624074
,

which can be formatted as
14:58:40
. In this formula,
2:23:17
represents not an absolute

time (2:23:17 AM) but an interval of time (2 hours, 23 minutes, and 17 seconds). This format

is perfectly acceptable to Excel.

TROUBLESHOOTING

Excel displays my time as #####

Usually, a cell full of number signs means the cell isn’t wide enough to show its

displayed contents. But Excel can’t display negative numbers as dates or times. If the

result of a date or time calculation is negative and you attempt to display this result

in a date or time format, you will see a cell full of number signs, no matter how much

you widen the cell. This typically happens when you subtract a later time of day from

an earlier time of day. You can work around the problem by converting the result to

elapsed hours. To do that, multiply the result by 24 and display it in a numeric format,

not a date or time format.

Working with date and time functions

Using the Excel date and time functions, you can perform worksheet calculations quickly

and accurately. For example, if you use your worksheet to calculate your company’s

monthly payroll, you might use the HOUR function to determine the number of hours

worked each day and the WEEKDAY function to determine whether employees should be

paid at the standard rate (for Monday through Friday) or at the overtime rate (for Saturdays

and Sundays).

In the following sections, we explore a few of the most useful date and time functions in

detail. You can access all 20 of the date and time functions available in Excel by clicking the

Date & Time button on the Formulas tab on the ribbon.

Using the TODAY and NOW functions

You can type
=TODAY( )
in a cell or a formula to insert the serial value of the current

date. If you type the function in a cell with the General format (which is the default), Excel

displays the resulting value in mm/dd/yyyy format. Although this function takes no

arguments, you must remember to include the empty parentheses. (Remember that
arguments