Microsoft Office Tutorials and References

In Depth Information

**Time-Related Functions**

When you enter the preceding formula, Excel formats the cell to display the date only.

To see the time, you’ll need to change the number format to one that displays a date

and a time.

To enter the current date and time into a cell that doesn’t change when the worksheet

recalculates, press Ctrl+; (semicolon), space, Ctrl+Shift+: (colon), and then press Enter.

The TIMEVALUE function converts a text string that looks like a time into a time serial number.

This formula returns 0.2395833333, which is the time serial number for 5:45 AM:

=TIMEVALUE(“5:45 am”)

To view the result of this formula as a time, you need to apply number formatting to the cell. The

TIMEVALUE function doesn’t recognize all common time formats. For example, the following

formula returns an error because Excel doesn’t like the periods in “a.m.”

=TIMEVALUE(“5:45 a.m.”)

Summing times that exceed 24 hours

Many people are surprised to discover that when you sum a series of times that exceed 24 hours,

Excel doesn’t display the correct total. Figure 6-6 shows an example. The range B2:B8 contains

times that represent the hours and minutes worked each day. The formula in cell B9 is

=SUM(B2:B8)

Figure 6-6:
Incorrect cell formatting makes the total appear incorrectly.