Apex Code Development You need to sign in to do that
Don't have an account?
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
)
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
)
Assuming the days in the partial starting and ending months would sum for one month, the following solution should work: 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
Assuming the days in the partial starting and ending months would sum for one month, the following solution should work: 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
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).
Thanks for your help!
Shannon