Microsoft Office Tutorials and References

In Depth Information

**Displaying any time**

Formulas that use the
NOW
function are updated only when the worksheet is recalculated, such as when you open the

i le or press F9. The time comes from your computer’s clock, so if the clock is wrong, the formula will return an

incorrect time.

16

To enter a time stamp (that doesn’t change) into a cell, press Ctrl+Shift+: (colon).

Displaying any time

One way to enter a time value into a cell is to just type it, 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 i rst 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 41612.7708333333 —

which represents 6:30 p.m. on December 4, 2013:

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