Adding Months And Years 

        

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.

 

 

 

Other Date And Time Related Procedures are described on the following pages.

        

Adding Months And Years

The DATEDIF Function

Date Intervals

Dates And Times

Date And Time Entry

Date And Time Arithmetic

Distributing Dates Over Intervals

Holidays

Julian Dates

Latitude And Longitude 

Overtime Hours And Timesheets


Time Zones In VBA

VBA Procedures For Dates And Times

Week Numbers

Worksheet Functions For Dates And Times

 

 

See the Dates And Times Topic Index For Information