### 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