 ShowAll Questionssorted byDate Posted Shannon 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 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.

Regards,
Anto Nirmal anto 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.

Regards,
Anto Nirmal

This was selected as the best answer Shannon 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 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.