Sometimes it is useful to be able to enter or calculate numbers that express a number
of years and months. For example, you may have a list of birthdays, and want to
determine the average age, in years and months. Or, perhaps, enter and sum a list of
numbers in yy.mm format. This page describes a variety of
formulas and methods for working with data in this format. Specifically, it examines
the DOLLARDE and DOLLARFR
functions. Both of these functions are part of the Analysis ToolPak add-in package, so you
must have the Analysis ToolPak installed.
Suppose we have a series of ages, expressed in years and months -- e.g., 30 years and 5
months. The most convenient way to enter these is yy.mm.
This number is not itself very useful, because the number 30.05 would be treated in
any arithmetic function to mean 30 and one-twentieth years, or 30 years, and about 18
days. The DOLLARDE function can be used to convert the .05
"fractional" part in to an actual decimal value. If cell A1 contains 30.05, the function
=DOLLARDE(A1,12)
will return 30.41667. The .41667 is the equivalent of 5/12, or five months of a
year.
For example consider the following data:
The range A1:A5 contains data as yy.mm . E.g., A1 indicates
30 years and 5 months. Note that when entering a single digit month, you must
enter the zero after the decimal place. For example, you must enter .01 for one month,
since .1 would be interpreted to mean 10 months, not 1 month.
See the Fractional Arithmetic page for more
information about the DOLLARDE and DOLLARFR
functions.