### Browse by categories

- All
- Collaboration
- Configuration & Data Management
- CPQ and Billing
- Customer Service & Support
- Desktop Integration
- Einstein Analytics
- Email Marketing
- Journey Management
- Mobile
- Mobile Messaging
- Packaging, Uploading & Installing Apps
- Reports & Dashboards
- Sales & Marketing
- Security
- Social Marketing
- Trailhead Challenges
- Additional Products

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

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

Steve MolisSo you basically want this?

## All Answers

Arijit MajeeHi Philipp,

Please check below link from an old question from this forum.

https://success.salesforce.com/answers?id=90630000000h4a7AAA

Adam JohnsonADDMONTHS(Start_Date__c, 50) Philipp MathisHi 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 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)

Adam JohnsonADDMONTHS(Start_Date__c, Subscription_Term__c) Philipp Mathisgreat 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 JohnsonI 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 Mathisit should add 1month and 6 days ->depending on the month (31 vs 30) etc. Steve MolisSo 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 Mathisexactely Steve MolisCrack open your Piggy Bank, this one is gonna cost you...

Steve MolisGive this a try

PS. You owe me WAY more than a beerPhilipp Mathisthanks 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 MolisNot for nuthin' but the requirements of this thing are changing more times than the plot of an M. Night Shyamalan movie Steve MolisSo the End Date is basically gonna be?

Steve MolisPS. 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

Steve Molis0.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 MolisSo you basically want this?

Steve MolisYou're welcome (https://www.youtube.com/watch?v=79DijItQXMM)Philipp Mathisonly 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 JohnsonYou're missing 2 )) you have your IF statements open but they aren't ever closed Steve MolisYour 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

Philipp 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 MolisSo you want to return a NULL result, like this?

Philipp Mathisyes 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 JohnsonThat's what NULL does Philipp Mathismany thanks both of you for your help Philipp Mathis@steve I would have another tricky date related one:

currently in below formula we are using an average days in a year time constant->

365.2425This works only if the period (duration) is not very long and also the price numbers are not so high. the longer the period (the more years) the bigger the chance that there are slight differences of course.

Would there be a formula that instead of always dividing by 365.2425 no matter how many years and which years, would really look at the actualy days per year and calculate the instant average value of this?

example case:

if "valid from" would be selected 2019 and valid to would be 2022 -> the system should know that for 2019 there are 365 days, for 2020 there are 366, for 2021 there are 365 and for 2022 there are again 365, therefore it would be

365.25(365+366+365+365/4). and of course this should be scalable for any future dates and period that might gets selected.many thanks,

philipp

IF(YEAR(Valid_to__c)= YEAR(Valid_from__c),TotalPrice,

IF(NOT(ISBLANK( Valid_to__c )) && NOT(ISBLANK( Valid_from__c ) ),(TotalPrice / ROUND((( Valid_to__c - Valid_from__c +1)/365.2425),10)),

IF(ISBLANK( Valid_to__c ) && ISBLANK( Valid_from__c ),(TotalPrice / ROUND (((Opportunity.Valid_to_Platform__c - Opportunity.Valid_from_Platform__c+1) /

365.2425),10)),0))),0)Steve MolisSorry for the late reply, so this is what I was thinking to get the exact count of days from the Valid_from__c until the Valid_to__c