Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting time figures into 24-hour format & sorting them... | Excel Worksheet Functions | |||
Converting Numbers to Time | Excel Worksheet Functions | |||
Time - converting HH:MM:SS to Minutes | Excel Worksheet Functions | |||
Can't stop time duration [hh]:mm:ss converting to time of day | Excel Discussion (Misc queries) | |||
Converting time to numbers | Excel Discussion (Misc queries) |