Skip to main content
Topics#Data ManagementPergunta de Glen Huckins
Glen Huckins (Thompson Pump & Manufacturing Co) perguntou em #Data Management
3 de mar. de 2021, 14:30

How to calculate the difference between two dates/times during office hours only to the tenth of an hour

I am trying  to calculate the difference between two dates/time field during office hours only to the tenth of an hour 

 

(.e. 2.6) Office hours are 8:00am to 5:00pm. 

 

I found the below but my efforts to alter have been unsuccessful. Any help is greatly appreciated. 

 

I found the below formula but it rounds to the hour. 

 

ROUND( 8 * (

 

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

 

MIN(5,

 

MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) +

 

MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )

 

)

 

) -

 

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

 

MIN( 5,

 

MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) +

 

MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )

 

)

 

)

 

)

 

, 0 )
7 respostas
  1. Tony Trinh
    3 de mar. de 2021, 16:00
    In that link I posted above, the best answer has some logic that says "if this date time is past a certain hour of the day or before a certain hour of the day, hard code this to the start of your day or end of your day"

     

    Replace your Date_Time__c in that formula with 

    IF(HOUR(TIMEVALUE(Date_Time__c - 5/24)) < 8, DATETIMEVALUE(TEXT(DATEVALUE(Date_Time__c)) & " 13:00:00"),
    IF(HOUR(TIMEVALUE(Date_Time__c - 5/24)) > 18, DATETIMEVALUE(TEXT(DATEVALUE(Date_Time__c)) & " 22:00:00"),
    Date_Time__c))

    So it will look like this:

    ROUND( 9 * (
    ( 5 * FLOOR( ( DATEVALUE( End_Date_Time__c ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5,
    MOD( DATEVALUE( End_Date_Time__c ) - DATE( 1900, 1, 8), 7) +
    MIN( 1, 24 / 9 * ( MOD( IF(HOUR(TIMEVALUE(End_Date_Time__c - 5/24)) < 8, DATETIMEVALUE(TEXT(DATEVALUE(End_Date_Time__c)) & " 13:00:00"), IF(HOUR(TIMEVALUE(End_Date_Time__c - 5/24)) > 18, DATETIMEVALUE(TEXT(DATEVALUE(End_Date_Time__c)) & " 22:00:00"), End_Date_Time__c)) - DATETIMEVALUE( '1900-01-08 13:00:00' ), 1 ) ) )
    )
    ) -
    ( 5 * FLOOR( ( DATEVALUE( Start_Date_Time__c ) - DATE( 1900, 1, 8) ) / 7) +
    MIN( 5,
    MOD( DATEVALUE( Start_Date_Time__c  ) - DATE( 1996, 1, 1), 7 ) +
    MIN( 1, 24 / 9 * ( MOD( IF(HOUR(TIMEVALUE(Start_Date_Time__c - 5/24)) < 8, DATETIMEVALUE(TEXT(DATEVALUE(Start_Date_Time__c)) & " 13:00:00"), IF(HOUR(TIMEVALUE(Start_Date_Time__c - 5/24)) > 18, DATETIMEVALUE(TEXT(DATEVALUE(Start_Date_Time__c)) & " 22:00:00"), Start_Date_Time__c))- DATETIMEVALUE( '1900-01-08 13:00:00' ), 1) ) )
    )
    )
    )
    , 1 )
Escrever uma resposta...
0/9000