Renewal Terms Question

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I am trying to set up a field that returns Next Renewal Date based on 3 pieces of information: Initial Contract Date, Initial Contract End Date, and Renewal Contract Duration. Example: My initial contract is signed in Sep 2012 for 2 years, and then it was automatically renewed every year since. The initial contract end date can be in the past or future. I don't know what the last renewal date is, that needs to be calculated.
Photo of Natasa

Natasa

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
Try this:

If(
  Today() < [Initial Contract Date],
    [Initial Contract Date],
  [Initial Contract Date] <= Today() and Today() < [Initial Contract End Date],
    [Initial Contract End Date],
  [Initial Contract End Date] < Today(),
     [Initial Contract End Date] + Ceil([Initial Contract End Date] - Today(), [Renewal Contract Duration])

)

I have highlighted the breakpoints for comparison as to where Today() falls.
Photo of Natasa

Natasa

  • 0 Points
I altered your formula a little, and it worked - thanks!
If(
Today()<=[Initial Contract Expiration Date],[Initial Contract Expiration Date],
If(
[Initial Contract Expiration Date]<Today(),
[Initial Contract Expiration Date]+Ceil(Today()-[Initial Contract Expiration Date],
[AutoRenewal Term (Days)])
))
Photo of Natasa

Natasa

  • 0 Points
I altered your formula a little, and it worked - thanks!
If(
Today()<=[Initial Contract Expiration Date],[Initial Contract Expiration Date],
If(
[Initial Contract Expiration Date]<Today(),
[Initial Contract Expiration Date]+Ceil(Today()-[Initial Contract Expiration Date],
[AutoRenewal Term (Days)])
))
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
Fantastic - to celebrate I am going to slack off the rest of the week.