problem summing columns with dates

[lampatmyfeet]

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(DATEDIF(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

 

[Pete_UK]

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

 

[lampatmyfeet]

Pete,

This formulas work great also. Sorry about the error with the number to
round from; you were correct. The unusual rounding regime is based on school
teachers schedules. Any time worked >= nine months is considered one year
due to school year calendar. Adding the time above the twelve months back to
the month column is important because it helps out with partial year credit
and may help someone retire sooner.

Your answer of 31 years and 2 months is correct I simply carried an extra one.

I really appreciate the help with this as working with dates, in this
manner, was a new foray for me.