Office 2007 Bible

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

Function	Description
HOUR	Converts a serial number to an hour
MINUTE	Converts a serial number to a minute
MONTH	Converts a serial number to a month
NOW	Returns the serial number of the current date and time
SECOND	Converts a serial number to a second
TIME	Returns the serial number of a particular time
TIMEVALUE	Converts 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 ...