LiveCycle Designer ES4 formula to count number of months between dates--including a calc for days=>15 means you count the month
I'm working on a formula in Excel, to make sure the logic is correct -- but eventually I would like to put it into a LiveCycle Designer form.
We count the actual number of months between two dates to calculate retention points for employment layoffs. If you work 15 or more days in a month, the month is counted. So, assuming non-leap year, if the start date is February 10, then February should be counted. If the start date is February 20, then February should not be counted. Most standard functions assume 30 day months, and I need to be able to include that =>15 calculation to the beginning and end date.
The way I'm tackling it in Excel is by starting with individual formulas to check the logic. Once I'm sure I have that right, I could make a nested formula (I think -- haven't tried yet). Any simplification you could offer would be welcome -- plus guidance on if this could even be attempted in LiveCycle Designer, and if it can, resources to help me write the Javascript or FormCalc. I'm willing to watch tutorials or read articles about how to do it -- but I'm not finding anything on my own.
Thanks!
Cell A2 - Start date
This is entered by the user
Cell B3 - Last day of the start date
=EOMONTH(A3,0)
Cell C3 - # days in start month
=B3-A3
Cell D3 - Returns "1" if start month should be counted
=IF(C3>=15,"1","0")
Cell E3 - End date
User entry
Cell F3 - Last day of end month
=EOMONTH(E3,0)
Cell G3 - # of days in the end month
=F3-E3
Cell H3 - Returns "1" if end month should be counted
=IF(G3>=15,"0","1")
Cell I3 - # of whole months between start and end date
=IF(DAY(E3)>=DAY(A3),0,-1)+(YEAR(E3)-YEAR(A3))*12+MONTH(E3)-MONTH(A3)
Cell J3 - Total # of months (whole months + partial months)
=SUM(I3+H3)
Cell K3 - Total yrs/months
=INT(J3/12) & " years, " & MOD(J3,12) & " months"
Formula to calculate the total number of months for all lines entered
=SUM(J3:J11)
Formula to restate above number as years and months
=INT(J13/12) & " years, " & MOD(J13,12) & " months"
