Microsoft Office Tutorials and References

In Depth Information

SECOND

Returns the second of a time

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):

=NOW()-TODAY()

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-

rect time.

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:

=TIME(A1,B1,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.