Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problem summing columns with dates
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,"m d")=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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problem summing columns with dates
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problem summing columns with dates
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. -- LAMP "Pete_UK" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing between 2 dates | Excel Worksheet Functions | |||
Summing values b/t two dates | Excel Discussion (Misc queries) | |||
Summing weekending dates | Excel Worksheet Functions | |||
problem with summing | Excel Worksheet Functions | |||
Summing moving ranges & dates | Excel Worksheet Functions |