Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Calculating End Date Using Number of Months, Minus One Day

Topic Labels: Formulas
Solved
Jump to Solution
1876 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!