Excel 2010 Bible

Part II: Working with Formulas and Functions

272

Determining a date’s quarter ...................................................................................

For financial reports, you may find it useful to present information in terms of quarters. The fol-

lowing formula returns an integer between 1 and 4 that corresponds to the calendar quarter for the

date in cell A1:

=ROUNDUP(MONTH(A1)/3,0)

This formula divides the month number by 3 and then rounds up the result.

Time-Related Functions ....................................................................................................

Excel also includes a number of functions that enable you to work with time values in your formu-

las. This section contains examples that demonstrate the use of these functions.

Table 12.5 summarizes the time-related functions available in Excel. These functions work with

date serial numbers. When you use the Insert Function dialog box, these functions appear in the

Date & Time function category.

TABLE 12.5

Time-Related Functions

Function Description

HOUR Returns the hour part of a serial number

MINUTE Returns the minute part of a serial number

NOW Returns the serial number of the current date and time

SECOND Returns the second part of a serial number

TIME Returns the serial number of a specified 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 quick-

est way is to choose Home ➪ Number ➪ Format Number and select Time from the drop-down

list.