Help

Calculating End Date Using Number of Months, Minus One Day

Topic Labels: Formulas
Solved
Jump to Solution
1655 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Ryan_Hagerty
4 - Data Explorer
4 - Data Explorer

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!

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

Wrap it in another DATEADD() function:

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

See Solution in Thread

2 Replies 2
Kamille_Parks
16 - Uranus
16 - Uranus

Wrap it in another DATEADD() function:

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

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