Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

LiveCycle Designer ES4 formula to count number of months between dates--including a calc for days=>15 means you count the month


Level 3

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.


Cell A2 - Start date

This is entered by the user

Cell B3 - Last day of the start date


Cell C3 - # days in start month


Cell D3 - Returns "1" if start month should be counted


Cell E3 - End date

User entry

Cell F3 - Last day of end month


Cell G3 - # of days in the end month    


Cell H3 - Returns "1" if end month should be counted


Cell I3 - # of whole months between start and end date


Cell J3 - Total # of months (whole months + partial months)


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


Formula to restate above number as years and months

=INT(J13/12) & " years, " & MOD(J13,12) & " months"


0 Replies