Return to Question

deleted 10 characters in body; edited tags

Source Link

edited Apr 7, 2017 at 18:24

151.3k
38
247
431

I am trying to determine the variance (in months) between an actual date and a planned date.

The actual date can be either before or after the planned date, and this is what is making this difficult.

I have the following formula to determine the difference in months

TEXT( 
IF(NOT(ISBLANK(Reference_Data_Item__c)), 
(((YEAR(Stage_6_Complete__c) - YEAR(Stage_6_Complete_DB_Forecast_Date__c) - 1) * 12) 
+ 
(12 - MONTH(Stage_6_Complete_DB_Forecast_Date__c) + 1) 
+ 
MONTH(Stage_6_Complete__c) 
- 1), 0)) & " Months"

This formula works fine for actual dates that end up being passed the planned date, but if the actual date ends before the planned date the variance has an extra 12 months added to it.

I would normally simply add a check to see which date comes first, but this is pushing me over the 5000 character limit salesforce imposes.

Does anyone have any ideas how I can get the difference while staying under the 5000 limit?

Thanks

I am trying to determine the variance (in months) between an actual date and a planned date.

The actual date can be either before or after the planned date, and this is what is making this difficult.

I have the following formula to determine the difference in months

TEXT( 
IF(NOT(ISBLANK(Reference_Data_Item__c)), 
(((YEAR(Stage_6_Complete__c) - YEAR(Stage_6_Complete_DB_Forecast_Date__c) - 1) * 12) 
+ 
(12 - MONTH(Stage_6_Complete_DB_Forecast_Date__c) + 1) 
+ 
MONTH(Stage_6_Complete__c) 
- 1), 0)) & " Months"

This formula works fine for actual dates that end up being passed the planned date, but if the actual date ends before the planned date the variance has an extra 12 months added to it.

I would normally simply add a check to see which date comes first, but this is pushing me over the 5000 character limit salesforce imposes.

Does anyone have any ideas how I can get the difference while staying under the 5000 limit?

Thanks

I am trying to determine the variance (in months) between an actual date and a planned date.

The actual date can be either before or after the planned date, and this is what is making this difficult.

I have the following formula to determine the difference in months

TEXT( 
IF(NOT(ISBLANK(Reference_Data_Item__c)), 
(((YEAR(Stage_6_Complete__c) - YEAR(Stage_6_Complete_DB_Forecast_Date__c) - 1) * 12) 
+ 
(12 - MONTH(Stage_6_Complete_DB_Forecast_Date__c) + 1) 
+ 
MONTH(Stage_6_Complete__c) 
- 1), 0)) & " Months"

This formula works fine for actual dates that end up being passed the planned date, but if the actual date ends before the planned date the variance has an extra 12 months added to it.

I would normally simply add a check to see which date comes first, but this is pushing me over the 5000 character limit salesforce imposes.

Does anyone have any ideas how I can get the difference while staying under the 5000 limit?

Source Link

asked Apr 7, 2017 at 18:23

Belgin Fish

Get Difference Between 2 Dates in Months Without Exceeding 5000 Characters

I am trying to determine the variance (in months) between an actual date and a planned date.

The actual date can be either before or after the planned date, and this is what is making this difficult.

I have the following formula to determine the difference in months

TEXT( 
IF(NOT(ISBLANK(Reference_Data_Item__c)), 
(((YEAR(Stage_6_Complete__c) - YEAR(Stage_6_Complete_DB_Forecast_Date__c) - 1) * 12) 
+ 
(12 - MONTH(Stage_6_Complete_DB_Forecast_Date__c) + 1) 
+ 
MONTH(Stage_6_Complete__c) 
- 1), 0)) & " Months"

This formula works fine for actual dates that end up being passed the planned date, but if the actual date ends before the planned date the variance has an extra 12 months added to it.

I would normally simply add a check to see which date comes first, but this is pushing me over the 5000 character limit salesforce imposes.

Does anyone have any ideas how I can get the difference while staying under the 5000 limit?

Thanks

salesforce-communities formula