Microsoft Office Tutorials and References

In Depth Information

**Using Other Functions**

For example, suppose you have a worksheet that

lists your employees, and you want to determine

how many years each employee will be with you

come the end of the year. You’ve decided to take

the year each person was hired, and subtract it

from 2011 to calculate the number of years

each employee has been working for you, come

12/31/2011. Assuming the first employee’s

hire date is in cell I10, you use this formula:

2011–YEAR(I10). The result is a date, which

appears rather confusing. Simply format the cell

using General format, and a value such as 12

appears.

Recalculating Your Formulas

In Chapter 2, “Working with Formulas,”

you learned how to control when Excel

recalculates a workbook. By default,

Excel recalculates a workbook automatically,

whenever you open or save it. In addition,

Excel normally recalculates formulas every

time a cell to which it refers changes. When

Excel recalculates a workbook, it updates

the results of all formulas, including the

TODAY and NOW functions. To force

recalculation at any time, click the Calculate Now

button on the Formulas tab.

Tip

NOW

The NOW function is similar to TODAY except

that it displays the current date and time. Like

the TODAY function, the NOW function does not

have any arguments. You type =NOW() into a

cell, and the current date and time are displayed.

When the worksheet is recalculated, the date

and time are updated.

Keep in mind that a formula such as

=MONTH(G4)–MONTH(F4) literally takes

the month number of the date in cell G4

minus the month number of the date in

cell F4. This may not be what you want.

For example, suppose G4 contains the

date 10/15/10, and cell F4 contains

the date 5/21/10. Take 10–5 and you get

5 months, although clearly 5 months has

not passed from 5/21/10 to 10/15/10.

A better formula might be =(G4–F4)/

365.25*12, which subtracts the two

dates to compute the number of days

between them, and then divides that

total by 365.25 to calculate the number

of years, which is then multiplied by 12

to get the number of months. Of course,

you’re going to end up with a fraction

of a month, so use ROUNDDOWN:

=ROUNDDOWN((G4–F4)/365.25*12).

DAY, MONTH, and YEAR

The DAY, MONTH, and YEAR functions work

similarly. Each has only one argument, Date, and

each extracts something from that date—the day,

the month, or the year. The syntax is

=DAY(Date)

=MONTH(Date)

=YEAR(Date)