Microsoft Office Tutorials and References

In Depth Information

**Determining a date’s quarter**

Determining a date’s quarter

For ﬁ nancial reports, you may ﬁ nd it useful to present information in terms of quarters.

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

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

Table 16.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 16.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 speciﬁ ed 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.

Or just use the following formula, which returns the current date and time. You can apply a

number format that shows the time only:

=NOW()