Microsoft Office Tutorials and References

In Depth Information

=TIME(9,80,15)

If you enter a value greater than 24 as the first argument for the TIME function, the res-

ult may not be what you expect. Logically, a formula such as the one that follows

should produce a date/time serial number of 1.041667 (that is, one day and one hour):

=TIME(25,0,0)

In fact, this formula is equivalent to the following:

=TIME(1,0,0)

You can also use the DATE function along with the TIME function in a single cell. The formula that follows

generates a date and time with a serial number of 41612.7708333333 — which represents 6:30 PM on Decem-

ber 4, 2013:

=DATE(2013,12,4)+TIME(18,30,0)

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 be-

cause Excel doesn't like the periods in
a.m.

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