Microsoft Office Tutorials and References

In Depth Information

**Time-Related Functions**

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.

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

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 following 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.

=TIME(9,80,15)

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

result 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 39420.7708333333 — which represents

6:30 PM on December 4, 2010: