Ask Search:
Philipp MathisPhilipp Mathis 

Formula to calculate a future end date based on a given start and duration info

hello community
I'm looking for a formula that returns a date in the future given a start date (manually to enter) plus a certain duration (in months). So e.g. the start date is today and in the term field I have 50months ->it should return me the exact date in the future (today + 50 months). As of now I only have a formula that gives me a rough estimate but is not considering the different leap years and is also not considering that month have differen amount of days. Is there a formula that properly caluclates that?

Many thanks for your help,
Philipp
Best Answer chosen by Philipp Mathis
Steve MolisSteve Molis
So you basically want this?
 
(ADDMONTHS(Subscription_Start_Date__c, FLOOR( Subscription_Term__c))
 +
FLOOR( 
(Subscription_Term__c - FLOOR( Subscription_Term__c )) *
DAY(ADDMONTHS(DATE(YEAR(Subscription_Start_Date__c),MONTH(Subscription_Start_Date__c), 01), Subscription_Term__c) - 1)
)) - 1

 

All Answers

Arijit MajeeArijit Majee
Hi Philipp,

Please check below link from an old question from this forum.
https://success.salesforce.com/answers?id=90630000000h4a7AAA
Adam JohnsonAdam Johnson
ADDMONTHS(Start_Date__c, 50)
Philipp MathisPhilipp Mathis
Hi Arijit
Thanks for the link. I tried the formula:
DATE(YEAR(Subscription_Start_Date__c) + FLOOR(Subscription_Term__c / 12) + 
           IF(MONTH(Subscription_Start_Date__c) + MOD(Subscription_Term__c / 12) <= 12,0,1),

        IF(MONTH(Subscription_Start_Date__c) + MOD(Subscription_Term__c / 12) <= 12,
            MONTH(Subscription_Start_Date__c) + MOD(Subscription_Term__c / 12),
            MONTH(Subscription_Start_Date__c) + MOD(Subscription_Term__c / 12) - 12), 

        DAY(Subscription_Start_Date__c))

but receiving the following error:
 Error: Incorrect number of parameters for function 'MOD()'. Expected 2, received 1

can you help

Thanks,
Philipp
Steve MolisSteve Molis
+++ @AdamJ and ADDMONTHS Function (that shit is THE BOMB!)

https://help.salesforce.com/articleView?id=customize_functions_a_h.htm&type=5#ADDMONTHS (https://help.salesforce.com/articleView?id=customize_functions_a_h.htm&type=5#ADDMONTHS
Example:	
ADDMONTHS (StartDate, 5)

Adds 5 months to the start date. For example, if the start date is September 20, 2017, the resulting date is February 20, 2018, If the start date is September 30, 2017, the resulting date is February 28, 2018.

 
Adam JohnsonAdam Johnson
ADDMONTHS(Start_Date__c, Subscription_Term__c)
Philipp MathisPhilipp Mathis

great thanks. but the addmonths is not working if you enter e.g. 0.5 months or 1.2 or 1.5 months...how can we incl. those scenarios

?

Adam JohnsonAdam Johnson
I mean, you said you wanted the exact date in the future for a number of months out - how does the systme calcualte an exact day for 0.2 of a month when months have dfiferent lengths?  Should 1.2 add 1 month, or add 1 month and 6 days? Do you want to round up/down?
Philipp MathisPhilipp Mathis
it should add 1month and 6 days ->depending on the month (31 vs 30) etc.
Steve MolisSteve Molis
So if the term is greater than 1 month, but not a whole month, you want to calculate the number of days in the final month an prorate it? 

Like if the term is 7.5 Months and the Last Month is Feruary (in a non Leap Year) 0.5 x 28 = 14 days

But if the Last Month is Arpil, June, September, November it's 0.5 x 30 = 15 days...  
Philipp MathisPhilipp Mathis
exactely
Steve MolisSteve Molis
Crack open your Piggy Bank, this one is gonna cost you...
 
Steve MolisSteve Molis
Give this a try
ADDMONTHS(Subscription_Start_Date__c, FLOOR( Subscription_Term__c))
 +
( 
(Subscription_Term__c - FLOOR( Subscription_Term__c )) *
DAY(ADDMONTHS(DATE(YEAR(Subscription_Start_Date__c),MONTH(Subscription_Start_Date__c), 01), Subscription_Term__c) - 1)
)
PS.  You owe me WAY more than a beer


 
Philipp MathisPhilipp Mathis
thanks a lot steve. for all the months having 31 days are you currently round down? e.g start date 1.8.19 and term is 0.8 it results in 24.8.19 (which is + 24 days)

Also I realized that if its e.g. 12month starting from 1.8.2019 it gives the end date 1.8.2020 which is actually correct but from a contractual term it should always be valid from 1.8.2020 for 1 year its until 31.7.2020
Steve MolisSteve Molis
Not for nuthin' but the requirements of this thing are changing more times than the plot of an M. Night Shyamalan movie
Steve MolisSteve Molis
So the End Date is basically gonna be? 
(Subscription_Start_Date__c + Subscription_Term__c) - 1 day

 
Steve MolisSteve Molis
PS.  This part of the Formula is just multiplying the number of days in the month by the remaining decimal value, I'm not doing any rounding up or down if there are 31 Days  
( 
(Subscription_Term__c - FLOOR( Subscription_Term__c )) *
DAY(ADDMONTHS(DATE(YEAR(Subscription_Start_Date__c),MONTH(Subscription_Start_Date__c), 01), Subscription_Term__c) - 1)
)

 
Steve MolisSteve Molis
0.8 x 31 = 24.8, so it would round up to 25  

If you always want the result to round down you would just add another FLOOR Function to the Formula
Steve MolisSteve Molis
So you basically want this?
 
(ADDMONTHS(Subscription_Start_Date__c, FLOOR( Subscription_Term__c))
 +
FLOOR( 
(Subscription_Term__c - FLOOR( Subscription_Term__c )) *
DAY(ADDMONTHS(DATE(YEAR(Subscription_Start_Date__c),MONTH(Subscription_Start_Date__c), 01), Subscription_Term__c) - 1)
)) - 1

 
This was selected as the best answer
Philipp MathisPhilipp Mathis
only had time to test it now. @steve thanks a lot!  :)
by the way can you tell me how I can easily see where a  `)` is missing in this:

IF(
CASE(PricebookEntry.Product2.Family,
'Rollout Services',1,
'Continuity Services',1,
'Education',1,
'Innovation Services',1,
0)=0,
IF( Term_months__c  <= 12,  TotalPrice , (TotalPrice  /  Term_months__c ) * 12)

 
thanks,
Philipp
Adam JohnsonAdam Johnson
You're missing 2 )) you have your IF statements open but they aren't ever closed
Steve MolisSteve Molis
Your formula is missing a final Else result at the end, for what to do if the CASE Statement in the first IF returns a 1
 
IF(
CASE(PricebookEntry.Product2.Family,
'Rollout Services',1,
'Continuity Services',1,
'Education',1,
'Innovation Services',1,
0) = 0,
IF( Term_months__c  <= 12,  
TotalPrice , 
(TotalPrice  /  Term_months__c ) * 12), Else? )

 
Philipp MathisPhilipp Mathis
@adam if Im adding another )
then I receive this error:  Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 2

@steve ok so in case it returns a 1 ->meaning first if is true then it shouldn't perform any calculation at all. how do I express this?
Steve MolisSteve Molis
So you want to return a NULL result, like this?
 
IF(
CASE(PricebookEntry.Product2.Family,
'Rollout Services',1,
'Continuity Services',1,
'Education',1,
'Innovation Services',1,
0) = 0,
IF( Term_months__c  <= 12,  
TotalPrice , 
(TotalPrice  /  Term_months__c ) * 12), 
NULL )

 
Philipp MathisPhilipp Mathis
yes or could we even not have that field populated at all? so we do not even need to write Null in that case but simple the field value is not populated and remains blank
Adam JohnsonAdam Johnson
That's what NULL does
Philipp MathisPhilipp Mathis
many thanks both of you for your help