Can a formula be written to have a date automatically update dependent on information in two other fields?

Can you provide a little more detail specific to 'how many days (how often)' it renews? Formulas will probably be the easiest - but keep in mind that using formulas means that your expiration date can move - meaning that you won't have a history of prior / older expiration dates.

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base

I am good with not having a history of prior expiration.  A lot renew on a monthly basis automatically, however some renew automatically yearly or every 5 years.  I would like the expiration date to automatically update based on if the contract auto-renews (current field, could change to a check box if that would make the formula easier) and the number of days indicated in the renewal days (current field).

I assume 'Term' then is the original Basis of the contract? 

Something like this might help get you started ( this likely won't be your true end result ): 

If(
     [Renewal Exposure]="Auto-Renewal" and AdjustMonth([Effective Date],[Term])<Today(), AdjustMonth([Effective Date],[Term]) + [Renewal (Days)], AdjustMonth([Effective Date],[Term]))

Basically if it auto-renews, and you've passed what the 'original' expiration date, then add 12 months (your term from above) to the effective date, then add another 30 days. 

Otherwise just do the Effective Date + 12 months

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base

The effective date is the date the contract started, term is how long the  original contract is effective (12 months in the case above).  Based on this information does your formula hold true still?

What has to change in order for the expiration date to keep updating, in this case, every 30 days, until a termination date is entered or the status is changed to InActive?

Most of the formula still holds true. I added a bit to it and made it a little cleaner. 


//START//

var date initialExpiration = AdjustMonth([Effective],[Term]);
var number numberOfRenewals = ToDays(Today()-$initialExpiration endInitial) / [Renewal (Days)];

if( 
     [Renewal Exposure]="Auto-Renewal" and 
     Today() > $initialExpiration, 
$initialExpiration + Days(Ceil($numberOfRenewals)*[Renewal (Days)]),
$initialExpiration)

//END//

To explain it in a little more detail:

First - set up what the 'original' expiration date was - AdjustMonth([Effective],[Term]);
Next - in the event that you keep 'auto renewing', figure out how many renewals that actually was. How many times did you cross over into a new renewal period by taking how many days its been since your original expiry date and now --

ToDays(Today()-$initialExpiration endInitial) / [Renewal (Days)];

Then - pull it all back together at the end - if you have an auto-renewal, and I've already passed the original expiry date, then tack on the # of renewals (from above) you've hit, and add the number of days to it

Otherwise, just leave it as the original expiry date. 

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base

What if the number of renewals is infinite?  It keeps renewing until the status (which is a field) reads Inactive.

The extensions as its written above will go on in perpetuity. I don't know what field you would use in particular to 'close' the contract to stop that from happening - but you would add a piece in the numberOfRenewals bit to have it stop if you have a 'Termination Date' or equivalent. 

So something like:

var number numberOfRenewals = ToDays(Today()-$initialExpiration endInitial) / [Renewal (Days)];

would become instead:

var number numberOfRenewals =
ToDays(
if( not isnull([Termination Date]), [Termination Date], Today()) - $initialExpiration endInitial) / [Renewal (Days)]);

So in the event that you have actually closed the contract - the # of Renewals would calculate up to when you decided to close it - and set the expiration date up to that point instead of going on endlessly. 


Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base

Ok so what would the formula be based on these facts: infinite number of renewals, the original expiration date would stay and the formula would be written for the updated expiration date field.

//Original Expiry Date
AdjustMonth([Effective],[Term]);
//End

//Endless Renewals - Stopping once you enter a termination date - Updated Expiry Date

var date initialExpiration = AdjustMonth([Effective],[Term]);
var number numberOfRenewals = 
ToDays(
if( not isnull([Termination Date]), [Termination Date], Today()) - $initialExpiration) / [Renewal (Days)]);

if( 
     [Renewal Exposure]="Auto-Renewal" and 
     Today() > $initialExpiration, 
$initialExpiration + Days(Ceil($numberOfRenewals)*[Renewal (Days)]),
$initialExpiration)

//End

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base