Skip to main content

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!

Wrap it in another DATEADD() function:


DATEADD(

   DATEADD({Commencement}, {Term (mos)}, 'months'),

   -1,

   "days"

)

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!

​@Kamille_Parks11 I applied this formula to Rent Start and Rent End on a monthly basis, and it all works, except when Rent Starts is the end of a month.  

 

Rent Start = June 30;  Rent End says it’s July 29.  It’s the same for nearly all month ends.  Are you aware of this and if you do have a solution, I would highly appreciate your help on this.  

 

Here’s the formula I entered:

DATEADD(DATEADD({Rental - Start Date}, 1, "months"), -1, "days")

​@Khuned_Sachdev this use case is for someone who had a start date field that was always the first of the month. The formula I gave is always mathematically correct for that use case.

If you have a date field that ISN’T always the first day of the month and you need to calculate the last day of the month, you need to force it to always be so. You can do that with DateTimeParse:

DATEADD(
  DATEADD(
    DATETIME_PARSE({Rent Start}, 'YYYY-MM'), 
    '1', 'month'
  ),
  '-1','day'
)

 

Terms & Conditions