+ Start a Discussion
Shannon Andreas 1Shannon Andreas 1 

Future Date Formula Problem

Hello All!

I am using this formula to calculate the term in months from effective date to end date. Works great if the months are different. However, it appears to be adding a month if my effective and end dates are in the same month.

e.g. Effective Date: 11/23/2015
       End DAte: 11/22/2016
Calculating 13 month term.


Anyone know how I can resolve?

Data Type Formula    
Decimal Places 0    
IF(NOT(ISBLANK(Service_End_Date__c)) && NOT(ISBLANK(Service_Effective_Date__c)) 
,(((YEAR(Service_End_Date__c ) - YEAR(Service_Effective_Date__c ) - 1) *12) + (12 - MONTH(Service_Effective_Date__c) +1) + MONTH(Service_End_Date__c )) 
, null 
)
Best Answer chosen by Shannon Andreas 1
anto nirmalanto nirmal
Hi Shannon,

Assuming the days in the partial starting and ending months would sum for one month, the following solution should work:
IF(NOT(ISBLANK(Service_End_Date__c)) && NOT(ISBLANK(Service_Effective_Date__c)),(((YEAR(Service_End_Date__c ) - YEAR(Service_Effective_Date__c ) - 1) *12) + (12 - MONTH(Service_Effective_Date__c) ) + MONTH(Service_End_Date__c )), null)
Reason: The starting year's month calculation should not include +1, because in ending year we are accomodating the ending partial month.
So we should ignore the partial month in the first year.

So in this example if starting date is 11/23/2015, then we need to account for 1 month(12-11) for this year.
In the ending year, we should consider the partial ending month of november. So it should be 11. Final output would be 12(11+1)

Let me know if this helps.

As a common practice, if your question is answered, please choose 1 best answer.
Additionally you can give every answer a like if that answer is helpful to you.

Regards,
Anto Nirmal
 

All Answers

anto nirmalanto nirmal
Hi Shannon,

Assuming the days in the partial starting and ending months would sum for one month, the following solution should work:
IF(NOT(ISBLANK(Service_End_Date__c)) && NOT(ISBLANK(Service_Effective_Date__c)),(((YEAR(Service_End_Date__c ) - YEAR(Service_Effective_Date__c ) - 1) *12) + (12 - MONTH(Service_Effective_Date__c) ) + MONTH(Service_End_Date__c )), null)
Reason: The starting year's month calculation should not include +1, because in ending year we are accomodating the ending partial month.
So we should ignore the partial month in the first year.

So in this example if starting date is 11/23/2015, then we need to account for 1 month(12-11) for this year.
In the ending year, we should consider the partial ending month of november. So it should be 11. Final output would be 12(11+1)

Let me know if this helps.

As a common practice, if your question is answered, please choose 1 best answer.
Additionally you can give every answer a like if that answer is helpful to you.

Regards,
Anto Nirmal
 
This was selected as the best answer
Shannon Andreas 1Shannon Andreas 1
Here is the adjusted formula to fit my need:

IF(NOT(ISBLANK( [Contract].EndDate )) && NOT(ISBLANK([Contract].StartDate )),(((YEAR([Contract].EndDate  ) - YEAR([Contract].StartDate  ) - 1) *12) + (12 - MONTH([Contract].StartDate ) ) + MONTH([Contract].EndDate  )), null)

Getting the following error when I try to save.

The formula expression is invalid: Formula result is data type (Number), incompatible with expected data type (Date).
Shannon Andreas 1Shannon Andreas 1
I'm good with this Anto!!! I was confusing this one with another formula I was working on...duh! No wonder it didn't work.

Thanks for your help!

Shannon