O'Reilly logo

Office 2007 Bible by Lisa A. Bucki, Gavin Powell, Michael R. Irwin, Peter G. Aitken, Michael R. Groh, Cary N. Prague, Faithe Wempen, Herb Tyson, John Walkenbach

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Time-Related Functions

Excel also includes a number of functions that enable you to work with time values in your formulas. This section contains examples that demonstrate the use of these functions.

Table 16-5 summarizes the time-related functions available in Excel. When you use the Insert Function dialog box, these functions appear in the Date & Time function category.

Table 16-5. Time-Related Functions
FunctionDescription
HOURConverts a serial number to an hour
MINUTEConverts a serial number to a minute
MONTHConverts a serial number to a month
NOWReturns the serial number of the current date and time
SECONDConverts a serial number to a second
TIMEReturns the serial number of a particular time
TIMEVALUEConverts a time in the form of text to a serial number

Displaying the current time

This formula displays the current time as a time serial number (or as 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 select Time from the drop-down list.

Note

This formula is updated only when the worksheet is calculated.

Tip

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

Displaying any time

One way to enter ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required