Skip to main content
Topics#Data ManagementPergunta de Donny Reblitz
Donny Reblitz (Tech Data) perguntou em #Data Management
18 de jul. de 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 respostas
  1. Donny Reblitz (Tech Data)
    18 de jul. de 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 )
Escrever uma resposta...
0/9000