Microsoft Office Tutorials and References
In Depth Information
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:
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.”
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
Figure 6-6: Incorrect cell formatting makes the total appear incorrectly.