Philipp 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?

Philipp
Best Answer chosen by Philipp Mathis
Steve Molis
So you basically want this?

```(ADDMONTHS(Subscription_Start_Date__c, FLOOR( Subscription_Term__c))
+
FLOOR(
(Subscription_Term__c - FLOOR( Subscription_Term__c )) *
)) - 1```

Arijit Majee
Hi Philipp,

Philipp 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 Molis

```Example:

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.```

Philipp 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

?

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 Mathis
it should add 1month and 6 days ->depending on the month (31 vs 30) etc.
Steve 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 Mathis
exactely
Steve Molis
Crack open your Piggy Bank, this one is gonna cost you...

Steve Molis
Give this a try
```ADDMONTHS(Subscription_Start_Date__c, FLOOR( Subscription_Term__c))
+
(
(Subscription_Term__c - FLOOR( Subscription_Term__c )) *
)```
PS.  You owe me WAY more than a beer

Philipp 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 Molis
Not for nuthin' but the requirements of this thing are changing more times than the plot of an M. Night Shyamalan movie
Steve Molis
So the End Date is basically gonna be?
`(Subscription_Start_Date__c + Subscription_Term__c) - 1 day`

Steve 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 )) *
)```

Steve 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 Molis
So you basically want this?

```(ADDMONTHS(Subscription_Start_Date__c, FLOOR( Subscription_Term__c))
+
FLOOR(
(Subscription_Term__c - FLOOR( Subscription_Term__c )) *
)) - 1```

This was selected as the best answer
Philipp 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
You're missing 2 )) you have your IF statements open but they aren't ever closed
Steve 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 Mathis
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 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 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
That's what NULL does
Philipp Mathis
many 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.2425
This 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 Molis
Sorry 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

```(DATE(VALUE(TEXT(Valid_To__c)), 12 , 31) + 1)
-
DATE(VALUE(TEXT(Valid_From__c)), 01 , 01)```