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
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.
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:
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
Search JabSto ::

Custom Search