Converting sum of time

T

Tanya

What formula would I use to convert the following information into service
time? For example: 26 yrs 48 mos. 278 dys needs to be converted to 30 yrs
7mos 8 dys.
 
F

Fred Smith

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)
 
M

Myrna Larson

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)
 
R

Ron Rosenfeld

What formula would I use to convert the following information into service
time? For example: 26 yrs 48 mos. 278 dys needs to be converted to 30 yrs
7mos 8 dys.
Click to expand...

Since years and months have varying numbers of days, what is your definition of
a "year" and a "month".

Unless you have firm definitions, the answer can only be approximate if
displayed that way.

And I don't understand how you derive 7 months from your data.

If you defined a month as being 30 days, and a year as 360 days, then you could
get 30 yrs 9 mos 8 days.


--ron
 
M

Myrna Larson

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 future:

=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")
 
Top