Hello Lamp,

good to hear that the formulae are working well for you, although it
is a very strange rounding system that you want to impose. I see that
you have changed the formulae so that you are only counting up to 14
days and 9 months before rounding, though your description doesn't
match with this.

I've assumed that you want to sum rows 9 to 13 inclusive, though you
can change these ranges if you need to. Here's the formula for total
days (G14):

=IF(MOD(SUM(G9:G13),30)=15,0,MOD(SUM(G9:G13),30))

I've used 15 days as before, and assumed that a standard month is 30
days.

This is the formula you will need for the total months (F14):

=IF(MOD(SUM(F9:F13)+INT(SUM(G9:G13)/30) + IF(MOD(SUM(G9:G13),
30)=15,1,0),12)=9,0, MOD(SUM(F9:F13)+INT(SUM(G9:G13)/30) +
IF(MOD(SUM(G9:G13),30)=15,1,0),12))

Be wary of spurious line breaks in the newsgroups.

This final formula will give you the total years (in E14):

=SUM(E9:E13)+INT((SUM(F9:F13)+INT(SUM(G9:G13)/30)+ IF(MOD(SUM(G9:G13),
30)=15,1,0))/12)+IF(MOD(SUM(F9:F13)+ INT(SUM(G9:G13)/
30)+IF(MOD(SUM(G9:G13),30)=15,1,0),12)=9,1,0)

I get 31 2 0 - not sure how you get 32 !!

Hope this helps.

Pete

On May 7, 7:26*pm, lampatmyfeet
wrote:
I have a table where I am summing columns containing date information. *The
table looks like:

* * * * * * * *E * * * * * * F * * * * * * G
* * * * * * *years * * *months * * *days
* * * * * * * *1 * * * * * * *2 * * * * * * 9
* * * * * * * *0 * * * * * * *5 * * * * * * 6
* * * * * * * 29 * * * * * * 0 * * * * * * 0
* * * * * * * *0 * * * * * * *6 * * * * * * 2
total * * * 32 * * * * * * *2 * * * * * * 0

the years, months, days information is found by the following formulas that
Pete_UK showed me last week (thanks again Pete the formulas they work great).
*FYI (A9 is beginning date mm/dd/yyyy and C9 is ending date mm/dd/yyyy

years -
=DATEDIF(A9,C9,"Y")+IF(DATEDIF(A9,C9,"YM")+IF(DATE DIF(A9,C9,"MD")=14,1,0)*10,1,0)

months -
=IF(DATEDIF(A9,C9,"ym")+IF(DATEDIF(A9,C9,"md")=14 ,1,0)=9,0,DATEDIF(A9,C9,*"ym")+IF(DATEDIF(A9,C9," md")=14,1,0))

days - =IF(DATEDIF(A9,C9,"md")=14,0,DATEDIF(A9,C9,"MD"))

The problem I am now encountering is this: *When the days column =15 days I
am to add 1 to months and then return 0 in 'days total' or =45 days then add
2 and then return 0 in 'days total', else return sum total of column C; then
when the months column is =9 then add 1 to year and return 0 unless the
total months is between 13 & 16 then you would add 1 to year and return the
difference between the total months-12 (you would then repeat this process
for each multiple of 12 months (add 2 years and then return months; if total
months are between 9 and 12 you return 0 to months total) or if total months
is < 8 then return sum of months column

I have shown, in the example above, what the total should look lilke but
have been unable to get it. *This really has me stuck. *Thanks for any help

LAMP