Microsoft Office Tutorials and References
In Depth Information
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):
In fact, this formula is equivalent to the following:
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:
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 be-
cause Excel doesn't like the periods in a.m.