Skip to main content
Topics#Data ManagementDomanda di Donny Reblitz
Donny Reblitz (Tech Data) ha fatto una domanda in #Data Management
18 lug 2018, 20:34

Calculating Business Hours Between Two Dates - Help Needed

I'm trying to see if the community can help here. I have a couple of fields that use formulas to calculate the difference between two dates. The difference is calculated in terms of calendar hours and I'd like to revise this formula to take into account business hours (M-F 0800 - 2000).

 

Does anyone have a good way to do this?

 

(floor((PSC_Complete_Time__c - PSC_Assigned_Time__c)*24)) 

 

+ 

 

(((((PSC_Complete_Time__c - PSC_Assigned_Time__c)*24)-floor((PSC_Complete_Time__c - PSC_Assigned_Time__c)*24))*60)/100)
7 risposte
  1. Donny Reblitz (Tech Data)
    18 lug 2018, 21:08
    Thank you both! Yes, these are both date/time fields and that formula seems to work ok. Regarding the note that reads "You can change the eights in the formula...", I'm assuming that would NOT include the 8's in the DATE() part of the formula. So the 8's to update would be the following. Does that sound right?

     

    ROUND( 8 * (

     

    ( 5 * FLOOR( ( DATEVALUE( PSC_Assigned_Time__c ) - DATE( 1900, 1, 8) ) / 7) +

     

    MIN(5,

     

    MOD( DATEVALUE( PSC_Assigned_Time__c ) - DATE( 1900, 1, 8), 7) +

     

    MIN( 1, 24 / 12 * ( MOD( PSC_Assigned_Time__c - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )

     

    )

     

    )

     

    -

     

    ( 5 * FLOOR( ( DATEVALUE( PSC_Complete_Time__c ) - DATE( 1900, 1, 8) ) / 7) +

     

    MIN( 5,

     

    MOD( DATEVALUE( PSC_Complete_Time__c ) - DATE( 1996, 1, 1), 7 ) +

     

    MIN( 1, 24 / 12 * ( MOD( PSC_Complete_Time__c - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )

     

    )

     

    )

     

    ),

     

    0 )
Scrivi una risposta...
0/9000