+ Start a Discussion
Jeremy York 7Jeremy 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 ))
kryzkryz
Here you go https://help.salesforce.com/HTViewSolution?id=000004526
Jeremy York 7Jeremy York 7
Unfortunately, that does not help as I need the time value not the date value.