1

We have a custom Service Request object and have following fields

  • Date/time open

  • Date/time closed

  • Expected Resolution date

  • SLA Type

SLA Type can be calendar day or Business day.So we would like to report how many calendar/business days the service request exceeded the Expected Resolution date? So for those that were overdue, how many days late were they? And for those completed on time, how many days early were they ?

I want two saparate formula fields

  • SLA Missed days

  • SLA Met days

Below formula returns calendar days. I need to consider Business days if SLA Type = 'Business Days'

IF(
    IsClosed__c && (Expected_Resolution_Date__c > DateTimeClosed__c) && SLAType__c == 'Calendar Days', 
    Expected_Resolution_Date__c - DateTimeClosed__c,  
    0
)
0

look for 'Finding the Number of Business Days Between Two Dates' on this page

Update

IF( !IsClosed__c
/* can remove 2 lines below if the date fields are required or null values validation is in place */
 || ISBLANK(Expected_Resolution_Date__c)
 || ISBLANK(DateTimeClosed__c),
0,
IF( SLAType__c = 'Calendar Days',
 Expected_Resolution_Date__c - DateTimeClosed__c,
 /* business days calculations */
 (5 * ( FLOOR( ( Expected_Resolution_Date__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( Expected_Resolution_Date__c - DATE( 1900, 1, 8), 7 ) ) )
 -
 (5 * ( FLOOR( ( DateTimeClosed__c- DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DateTimeClosed__c - DATE( 1900, 1, 8), 7 ) ) )
 )
)
| improve this answer | |
0

This is the formula to calculate business days between two dates, just be aware that date_1 is the more recent date and date_2 is the earlier date:

(5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_1 - DATE( 1900, 1, 8), 7 ) ) ) - (5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_2 - DATE( 1900, 1, 8), 7 ) ) )
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.