calculate months between dates less 1 day is contract term = xx

WinaHath

I need to output the End Date based on contract term and subtract a day from the end date.

thank you!

KPH

Hi @WinaHath

How accurate do you need the result to be? If you can base the end date on month being a certain number of days (say 28) then you can do this easily using

=[Effective Date]@row + ([Contract Term (Months)]@row * 28) - 1

If the end date needs to be exact and the number of days needs to vary based on the length of the months after the effective date, then you can build a date using the DATE function. The resulting formula is a bit tricky to understand, so I will step through the process of creating it so you know what it is doing and can adjust it if needed. You start with something like:

=DATE(YEAR([Effective Date]@row), MONTH([Effective Date]@row) + [Contract Term (Months)]@row, DAY([Effective Date]@row))-1

This creates a date with a year of the effective date, a month of the effective date plus the contract term, and the day of the effective date. It then subtracts 1.

BUT (this is a big but)

It will only work if the effective date and end date are within the same year.

So, to account for that we need to add some IF logic. This will identify where adding the contract term to the month of the effective date will return a month that is more than 12 (and therefore in the next year).

=IF(MONTH([Effective Date]@row) + [Contract Term (Months)]@row > 12 ,"this is a problem","the formula above works here")

We can paste in the formula above for when the addition of the contract term months does not go into next year, and add your minus 1 day, like this:

=IF(MONTH([Effective Date]@row) + [Contract Term (Months)]@row > 12, "this is a problem", DATE(YEAR([Effective Date]@row), MONTH([Effective Date]@row) + [Contract Term (Months)]@row, DAY([Effective Date]@row)))-1

And then create a formula for the "problem" situation.

I don't know how long your contract lengths are. This will cope with any that span one new year, but you will need to extend it with another IF to for any that span more than 2 years.

= DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) - (12 - [Contract Term (Months)]@row), DAY([Effective Date]@row))

This creates a date with a year one year after the effective date, a month of the effective date minus 12 minus the contract term, and the day of the effective date.

Popping that into the formula above gives you:

=IF(MONTH([Effective Date]@row) + [Contract Term (Months)]@row > 12, DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) - (12 - [Contract Term (Months)]@row), DAY([Effective Date]@row)), DATE(YEAR([Effective Date]@row), MONTH([Effective Date]@row) + [Contract Term (Months)]@row, DAY([Effective Date]@row)))-1

Hope this all makes sense and does what you need.

KPH

Hi @WinaHath

How accurate do you need the result to be? If you can base the end date on month being a certain number of days (say 28) then you can do this easily using

=[Effective Date]@row + ([Contract Term (Months)]@row * 28) - 1

If the end date needs to be exact and the number of days needs to vary based on the length of the months after the effective date, then you can build a date using the DATE function. The resulting formula is a bit tricky to understand, so I will step through the process of creating it so you know what it is doing and can adjust it if needed. You start with something like:

=DATE(YEAR([Effective Date]@row), MONTH([Effective Date]@row) + [Contract Term (Months)]@row, DAY([Effective Date]@row))-1

This creates a date with a year of the effective date, a month of the effective date plus the contract term, and the day of the effective date. It then subtracts 1.

BUT (this is a big but)

It will only work if the effective date and end date are within the same year.

So, to account for that we need to add some IF logic. This will identify where adding the contract term to the month of the effective date will return a month that is more than 12 (and therefore in the next year).

=IF(MONTH([Effective Date]@row) + [Contract Term (Months)]@row > 12 ,"this is a problem","the formula above works here")

We can paste in the formula above for when the addition of the contract term months does not go into next year, and add your minus 1 day, like this:

=IF(MONTH([Effective Date]@row) + [Contract Term (Months)]@row > 12, "this is a problem", DATE(YEAR([Effective Date]@row), MONTH([Effective Date]@row) + [Contract Term (Months)]@row, DAY([Effective Date]@row)))-1

And then create a formula for the "problem" situation.

I don't know how long your contract lengths are. This will cope with any that span one new year, but you will need to extend it with another IF to for any that span more than 2 years.

= DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) - (12 - [Contract Term (Months)]@row), DAY([Effective Date]@row))

This creates a date with a year one year after the effective date, a month of the effective date minus 12 minus the contract term, and the day of the effective date.

Popping that into the formula above gives you:

=IF(MONTH([Effective Date]@row) + [Contract Term (Months)]@row > 12, DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) - (12 - [Contract Term (Months)]@row), DAY([Effective Date]@row)), DATE(YEAR([Effective Date]@row), MONTH([Effective Date]@row) + [Contract Term (Months)]@row, DAY([Effective Date]@row)))-1

Hope this all makes sense and does what you need.

WinaHath

wow!!! I wish I was smart like you! LOL! The challenge is that the contract terms vary. I had it figured out when I tracked the contract end term based on number of years but then I ran into a contract that has an 8-month duration.

WinaHath

IT WORKED!!!!!!😁 THANK YOU & HAPPY HOLIDAYS!

KPH

Wonderful. 😍 Happy Holidays to you as well!

WinaHath

okay, you are correct. it only worked where the contract term is = 12 months. bummer! I was stoked until I entered 36 months and I got an error.

KPH

It will work for any end date that is up to 1 year after the year of the effective date. For a contract that starts in January, you can go up to 23 months (Jan 2023 - Dec 2024 only spans 1 new year). 36 months will not work.

For longer contracts you can add another IF function at the start. You said you had a formula already for that situation. Use that formula if the contract terms months plus current month is more than 23, and the formula above if it is less.

WinaHath

Thank you! I need to have one formula that will count the number of months after the start date, less 1 day.

KPH

Hi

I am not sure if your comment means you have added to the formula and are all sorted now, or if you still need help. In case it is the latter, this is what I meant about adding another formula to work in situations where the contract length plus effective date spans more than one new year.

The original formula checks if the effective date + contract term is more than 12. You can add another IF after that to check whether it is more than 23. If so, you need to use your other formula to calculate the end date. If not, the rest of the original formula will be applied.

Depending on what formula you are using to calculate the end date for the longer contracts, you may need to extend this again for contracts that span 3 new years.

=IF(MONTH([Effective Date]@row) + [Contract Term (Months)]@row > 12, IF(MONTH([Effective Date]@row) + [Contract Term (Months)]@row > 23, Insert formula here, DATE(YEAR([Effective Date]@row) + 1, MONTH([Effective Date]@row) - (12 - [Contract Term (Months)]@row), DAY([Effective Date]@row))), DATE(YEAR([Effective Date]@row), MONTH([Effective Date]@row) + [Contract Term (Months)]@row, DAY([Effective Date]@row))) - 1

WinaHath

Thank you so much!