Skip to main content
Solved

Calculating End Date Using Number of Months, Minus One Day

  • August 3, 2021
  • 4 replies
  • 95 views
R
Forum|alt.badge.img+1

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!

Best answer by Kamille_Parks11

Wrap it in another DATEADD() function:

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

4 replies

Kamille_Parks11
Forum|alt.badge.img+27

Wrap it in another DATEADD() function:

DATEADD(
   DATEADD({Commencement}, {Term (mos)}, 'months'),
   -1,
   "days"
)
R
Forum|alt.badge.img+1
  • Ryan_Hagerty
  • Author
  • New Participant
  • August 3, 2021

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!

K
Forum|alt.badge.img+9

​@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")

Kamille_Parks11
Forum|alt.badge.img+27

​@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 & ConditionsAccessibility statement