Microsoft Office Tutorials and References
In Depth Information
Returns the second of a time
Returns a time for a specified hour, minute, and second
TIMEVALUE Converts a time in the form of text to an actual time value
Displaying the current time
This formula displays the current time as a time serial number (or a serial number without an associated date):
You need to format the cell with a time format to view the result as a recognizable time. The quickest way is to
choose Home ⇒ Number ⇒ Format Number and then select Time from the drop-down list.
You can also display the time, combined with text. The formula that follows displays this text: The current time
is 6:28 PM.
=”The current time is “&TEXT(NOW(),”h:mm AM/PM”)
These formulas are updated only when the worksheet is calculated. The time comes
from your computer's clock, so if the clock is wrong, the formulas will return an incor-
To enter a time stamp into a cell, press Ctrl+Shift+: (colon). Excel inserts the time as a
static value (it does not change).
Displaying any time using a function
Earlier in this chapter, I describe how to enter a time value into a cell: Just type it into a cell, making sure that
you include at least one colon (:). You can also create a time by using the TIME function. For example, the fol-
lowing formula returns a time comprising the hour in cell A1, the minute in cell B1, and the second in cell C1:
Like the DATE function, the TIME function accepts invalid arguments and adjusts the result accordingly. For
example, the following formula uses 80 as the minute argument and returns 10:20:15 AM. The 80 minutes are
simply added to the hour, with 20 minutes remaining.