You need to sign in to do that
Don't have an account?
Jeremy York 7
Help with excluding weekends from a date/time formula
Hello,
The formula below shows how many hours since the first email response on a case based on the created date of the case. How can I modify the formula so that if the createddate of the case falls on a Saturday or Sunday it bases the response time on the createddate being Monday at 8am instead of the datatime the case was created on Saturday or Sunday. The help desk hours are 8am to 8pm ET Monday thru Friday.
Example: Case is created on 4/9/16 at 2:53pm
First email response is on 4/11/16 at 8:22am
Email Response Time should be 0
(ROUND( 12 * (
( 5 * FLOOR( ( DATEVALUE( First_Email_Sent_Date__c ) - DATE( 1996,01,01) ) / 7) +
MIN(5,
MOD( DATEVALUE( First_Email_Sent_Date__c ) - DATE(1996,01,01), 7) +
MIN( 1, 24 / 12 * ( MOD( First_Email_Sent_Date__c - DATETIMEVALUE( '1996-01-01 13:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( CreatedDate ) - DATE( 1996,01,01) ) / 7) +
MIN( 5,
MOD( DATEVALUE( CreatedDate ) - DATE( 1996,01,01), 7 ) +
MIN( 1, 24 / 12 * ( MOD( CreatedDate - DATETIMEVALUE( '1996-01-01 13:00:00' ), 1) ) )
)
)
),
0 ))
The formula below shows how many hours since the first email response on a case based on the created date of the case. How can I modify the formula so that if the createddate of the case falls on a Saturday or Sunday it bases the response time on the createddate being Monday at 8am instead of the datatime the case was created on Saturday or Sunday. The help desk hours are 8am to 8pm ET Monday thru Friday.
Example: Case is created on 4/9/16 at 2:53pm
First email response is on 4/11/16 at 8:22am
Email Response Time should be 0
(ROUND( 12 * (
( 5 * FLOOR( ( DATEVALUE( First_Email_Sent_Date__c ) - DATE( 1996,01,01) ) / 7) +
MIN(5,
MOD( DATEVALUE( First_Email_Sent_Date__c ) - DATE(1996,01,01), 7) +
MIN( 1, 24 / 12 * ( MOD( First_Email_Sent_Date__c - DATETIMEVALUE( '1996-01-01 13:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( CreatedDate ) - DATE( 1996,01,01) ) / 7) +
MIN( 5,
MOD( DATEVALUE( CreatedDate ) - DATE( 1996,01,01), 7 ) +
MIN( 1, 24 / 12 * ( MOD( CreatedDate - DATETIMEVALUE( '1996-01-01 13:00:00' ), 1) ) )
)
)
),
0 ))
kryz
Here you go https://help.salesforce.com/HTViewSolution?id=000004526
Jeremy York 7
Unfortunately, that does not help as I need the time value not the date value.