function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Phillip MoseleyPhillip Moseley 

Formula that Calculates Start Date x Length of Terms to Equal Date minus 1 Day

I'm trying to create a field that will calculate the start date times the Length of Terms to give me a date that is minus one day from the total contract length.

For example:
Start Date 8/22/2014 x Year In Terms 12 would equal an End Date Field 8/21/2015
 

This would take out the manual process of having to enter a date one day less than the Start Date field.

Any help would be appreciated!

Thanks,
 


Best Answer chosen by Phillip Moseley
Jason Curtis NBSFDGJason Curtis NBSFDG
Hi, Phillip, is the length of terms measured in months or years?
Either way some good examples here: https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US (https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US)

Here is the formula for adding years to a date, minus a day.
Start_Date__c + (365.25 *  Term_in_Years__c ) - 1
This worked in the edge cases I tried, for example, off the top of my head these are the edge conditions:
  • The first of the month, i.e. 4/1/2014 with a 1 year term, then you need to go back a day to 3/31/15.
  • First of the year, 1/1/15 with a 2 year term: 12/31/16.
  • The 29th on a leap year, with terms that put it to a non-leap year and terms that put it on a leap year.

Test it out and see.
If there is a case this doesn't work than you'll need to build a more complex formula (like in the examples) that take into account leap years and the edge cases.

All Answers

Jason Curtis NBSFDGJason Curtis NBSFDG
Hi, Phillip, is the length of terms measured in months or years?
Either way some good examples here: https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US (https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US)

Here is the formula for adding years to a date, minus a day.
Start_Date__c + (365.25 *  Term_in_Years__c ) - 1
This worked in the edge cases I tried, for example, off the top of my head these are the edge conditions:
  • The first of the month, i.e. 4/1/2014 with a 1 year term, then you need to go back a day to 3/31/15.
  • First of the year, 1/1/15 with a 2 year term: 12/31/16.
  • The 29th on a leap year, with terms that put it to a non-leap year and terms that put it on a leap year.

Test it out and see.
If there is a case this doesn't work than you'll need to build a more complex formula (like in the examples) that take into account leap years and the edge cases.




This was selected as the best answer
Phillip MoseleyPhillip Moseley
Thanks for your response! Your suggestion below worked great!
Jason Curtis NBSFDGJason Curtis NBSFDG

Glad it worked, if you get a chance can you mark the best answer and that this is solved?
 

Best,
 

Jason
Phillip MoseleyPhillip Moseley
Thanks again Jason! I have updated your post as the best answer. Phillip