Calculating End Date Using Number of Months, Minus One Day

[Ryan_Hagerty]

Hi guys,

I’m trying to find a way to insert a formula that calculates the expiration date of leases based on the commencement date and the number of months that the contract is for. So far, I have this:

DATEADD({Commencement}, {Term (mos)}, ‘months’)

This calculates the expiration date almost perfectly, but I need to find a way to take one day off of the formula. A 12 month lease starting on 03/01/21 would return 03/01/22, but the expiration would actually be the last day of February, not the first day of March, if that makes sense.

How can I get the formula to subtract one day from the result?

Thank you!

[Kamille_Parks · Accepted solution]

Wrap it in another DATEADD() function:

DATEADD(
   DATEADD({Commencement}, {Term (mos)}, 'months'),
   -1,
   "days"
)

[Ryan_Hagerty]

Yes! Thank you! My brain could just not compute doing two functions together, I got so lost. I appreciate it!