On 2nd thought, if what the OP wants is not a calendar date but *elapsed
time*, measured in years, months, and days, Fred's formula isn't going to
work.

To use a simpler example, let's say we want to devise a formula that will
convert 0 years, 48 months, and 0 days to 4 years, 0 months, and 0 days.

For any formula that returns a date, the range for months with be 1-12, where
we want 0-11; and the range for days is 1-31, where we want 0-30. You can't
get 0 months and/or 0 days because there is no month 0 or day 0.

Fred's formula, =DATE(0,48,0), gives 11/30/1903 [sic!], or 3 years, 11 months,
and 30 days, not 4 years, 0 months, and 0 days. That seems to be a bug in
Excel's date routines. The formula is calculating the date that is 48 months
from the implied date of 12/31/1899; we should get 12/31/1903, not 11/30/1903!
In fact, if I put the date 1/1/1900 in a cell, say A1, then write the formula

=DATE(YEAR(A1),MONTH(A1)+48,0)

it gives the expected result of 12/31/1903.

But back to the OP's problem: If you are trying to calculated elapsed time,
because of the different month lengths and year lengths, you need to add the
specified numbers of years, months, and days to the relevant starting date,
and that probably isn't January 1, 1900. If you are calculating age, it would
be the date of birth. If you are calculating years of service, that would be
the employee's hire date.

Let's say you have a hire date in A1. Years of service is in B1, months in C1,
and days in D1. This formula will convert to a calendar date the correct
number of years, months, and days in the futu

=DATE(YEAR(A1),MONTH(A1)+B1*12+C1,DAY(A1)+D1)

With that formula in E1, the length of service is

Years: =DATEDIF(A1,E1,"y")
Months: =DATEDIF(A1,E1,"ym")
Days: =DATEDIF(A1,E1,"md")


On Thu, 21 Apr 2005 21:48:00 -0500, Myrna Larson
wrote:

That, of course gives this result: 9/4/1930. To get 30 years, 9 months, and
4
days, you need 3 more formulas. Assuming you have 26, 48, and 278, in A1:C1,
you can put the formula =DATE(A1,B1,C1) in D1. Then in E1 use =YEAR(D1)-1900,
in F1 =MONTH(D1), in G1 =DAY(D1)


On Thu, 21 Apr 2005 19:07:56 -0600, "Fred Smith"
wrote:

Use the Date function.

=date(26,48,278)

will convert to the proper date (which, by the way, is 30 years, 9 months, 4
days)