Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Tanya
 
Posts: n/a
Default Converting sum of time

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.
  #2   Report Post  
Fred Smith
 
Posts: n/a
Default

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)

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Tanya" wrote in message
...
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.



  #3   Report Post  
Myrna Larson
 
Posts: n/a
Default

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)


  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 21 Apr 2005 13:27:02 -0700, "Tanya"
wrote:

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.


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
  #5   Report Post  
Myrna Larson
 
Posts: n/a
Default

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)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting time figures into 24-hour format & sorting them... Clint Johnson Excel Worksheet Functions 1 February 23rd 05 02:23 AM
Converting Numbers to Time MB Excel Worksheet Functions 4 February 19th 05 03:39 AM
Time - converting HH:MM:SS to Minutes Noel S Pamfree Excel Worksheet Functions 3 January 8th 05 05:26 PM
Can't stop time duration [hh]:mm:ss converting to time of day Jack Cutrone Excel Discussion (Misc queries) 7 December 28th 04 12:23 AM
Converting time to numbers DLSQestions Excel Discussion (Misc queries) 2 December 20th 04 07:07 PM


All times are GMT +1. The time now is 03:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"