Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum of dates
I posted this question yesterday, (and yes, I realized my calculation error
after I posted. But, thanks for the corrections) but I may not have been clear about what information I am trying to calculate. My spreadsheet is formatted as follows: A1 B1 D1 E1 F1 From To Years Months Days 10/16/198512/31/1989 4 2 16 1/1/1990 10/11/1990 0 9 11 2/19/1991 9/30/1991 0 7 12 Total 4 18 39 To get the years, months and days I am using the following formulas: =DATEDIF(A4,B4,"Y"), =DATEDIF(A4,B4,"YM"), =DATEDIF(A4,B4 +1,"MD") I now need the formula to convert the 4yrs, 18mos, 39days into 5yrs, 7mos, 30 days or 5yrs, 8mos preferrably. (Did I get it right this time?) :) Thanks for your help! |
#2
|
|||
|
|||
Assume the sums of rows 2,3 and 4 are in D7, E7 and F7....
D8 = D7+INT(E7/12) E8 = MOD(+E7+INT(((F7-F8)/30)),12) F8 = MOD(F7,30) HTH, Gary Brown "Tanya" wrote in message ... I posted this question yesterday, (and yes, I realized my calculation error after I posted. But, thanks for the corrections) but I may not have been clear about what information I am trying to calculate. My spreadsheet is formatted as follows: A1 B1 D1 E1 F1 From To Years Months Days 10/16/198512/31/1989 4 2 16 1/1/1990 10/11/1990 0 9 11 2/19/1991 9/30/1991 0 7 12 Total 4 18 39 To get the years, months and days I am using the following formulas: =DATEDIF(A4,B4,"Y"), =DATEDIF(A4,B4,"YM"), =DATEDIF(A4,B4 +1,"MD") I now need the formula to convert the 4yrs, 18mos, 39days into 5yrs, 7mos, 30 days or 5yrs, 8mos preferrably. (Did I get it right this time?) :) Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Excel not recognizing Dates properly. | Excel Discussion (Misc queries) | |||
How sort dates just by day/month and NOT year if all 3 given in ce | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel |