Automated renewal dates based on a selected duration?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I would like to set up an automated date field that returns a desired due date based on a duration selected on another field, i.e. Quarter, Month, Year.  For example, if I were to select a contract start date of Sept. 1st and Quarterly is the selected duration, how would I get the renewal date field to automatically return Dec. 1st?

Photo of Chris_MCD

Chris_MCD

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 650 Points 500 badge 2x thumb
Say you have the following fields:

[Contract Start Date] - Date field

[Renewal Frequency] - Text-Multiple Choice field, with the values:  Month, Quarter, Year

Now create a new field:

[Renewal Date] - Formula Date field

with the following formula:

var Duration ElapsedTime = Today() - [Contract Start Date];

var Date LastRenewalDate = Case ([Renewal Frequency],

"Month", AdjustMonth([Contract Start Date], Floor (ToDays($ElapsedTime) / 30) ),

"Quarter", AdjustMonth([Contract Start Date], 3 * Floor (ToDays($ElapsedTime) / 90) ),

"Year", AdjustYear([Contract Start Date], Floor (ToDays($ElapsedTime) / 365) )

);

var Date NextRenewalDate = Case ([Renewal Frequency],

"Month", AdjustMonth( $LastRenewalDate, 1 ),

"Quarter", AdjustMonth( $LastRenewalDate, 3 ),

"Year", AdjustYear( $LastRenewalDate, 1 )

);

$NextRenewalDate



If you want, you can collapse this into a smaller formula, or break it out into separate intermediate fields, etc.

First you calculate the when the LastRenewalDate was.  Then you calculate the NextRenewalDate by adding the appropriate # of months, or years, depending on [Renewal Frequency].