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




Custom Search