Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate the solution time between two log moments based on working hours and business days

I want to calculate the solution time between two log moments (date_time finished  AND date_time required) based on working hours and business days. By this I mean that weekends, vacations, public ho...
Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    Anonymous,

     

    Try this calculated column. You can expand the SWITCH functions in variables vStartDatetimeAdj and vEndDatetimeAdj to handle additional scenarios (e.g., midnight).

     

    Workday Seconds = 
VAR vWorkdayStartHour = 8
VAR vWorkdayEndHour = 17
VAR vColumn1 = bw_Cofiwerkorders[Date_time required]
VAR vColumn2 = bw_Cofiwerkorders[Date_time finished]
VAR vWorkdayHours = vWorkdayEndHour - vWorkdayStartHour
// the earlier of the two columns
VAR vStartDatetime = MIN ( vColumn1, vColumn2 ) 
// the later of the two columns
VAR vEndDatetime = MAX ( vColumn1, vColumn2 )
VAR vStartDate = INT ( vStartDatetime )
VAR vEndDate = INT ( vEndDatetime )
// count the workdays between StartDate and EndDate (non-inclusive)
VAR vWorkdaysBetween =
    CALCULATE (
        COUNTROWS ( DimDate ),
        DimDate[Date] > vStartDate,
        DimDate[Date] < vEndDate,
        DimDate[Weekday Flag] = 1
    )
// total workday seconds between StartDate and EndDate (non-inclusive)
VAR vWorkdaySecondsBetween = vWorkdaysBetween * vWorkdayHours * 60 * 60
// Start Date beginning of day
VAR vStartDateBOD = vStartDate + TIME ( vWorkdayStartHour, 0, 0 )
// Start Date end of day
VAR vStartDateEOD = vStartDate + TIME ( vWorkdayEndHour, 0, 0 )
// End Date beginning of day
VAR vEndDateBOD = vEndDate + TIME ( vWorkdayStartHour, 0, 0 )
// End Date end of day
VAR vEndDateEOD = vEndDate + TIME ( vWorkdayEndHour, 0, 0 )
VAR vStartDatetimeAdj = 
    SWITCH ( TRUE (),
        vStartDatetime < vStartDateBOD, vStartDateBOD,
        vStartDatetime > vStartDateEOD, vStartDateEOD,
        vStartDatetime
    )
VAR vEndDatetimeAdj = 
    SWITCH ( TRUE (),
        vEndDatetime < vEndDateBOD, vEndDateBOD,
        vEndDatetime > vEndDateEOD, vEndDateEOD,
        vEndDatetime
    )
VAR vStartDateSeconds = DATEDIFF ( vStartDatetimeAdj, vStartDateEOD, SECOND )
VAR vEndDateSeconds = DATEDIFF ( vEndDateBOD, vEndDatetimeAdj, SECOND )
VAR vTotalWorkdaySeconds =
    SWITCH ( TRUE (),
        vStartDate = vEndDate, DATEDIFF ( vStartDatetimeAdj, vEndDatetimeAdj, SECOND ),
        vStartDateSeconds + vWorkdaySecondsBetween + vEndDateSeconds
    )
VAR vResult = IF ( vColumn1 < vColumn2, vTotalWorkdaySeconds, vTotalWorkdaySeconds * -1 )
RETURN
    vResult

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi DataInsights 


Thank you, the formula works great but I found a few problems.
1: If date_time required and date_time finished are on the same day/date then the formula adds automatically 32400 seconds(1 workday) to the result. This is the main issue.

And if it is possible I would like to fix the two following problems:
1: When one of the date/times is 00:00:00 then the formula does -28800.
2: When one of the times is noted outside of the working hours, for example 17:22:00, then the formule subtracts 22 minutes/1320 seconds from the total result. I would like to see that the formula ignores that 22 minutes and sees it like 17:00:00 or 8:00:00.


DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
4 years ago

Anonymous,

 

Try this calculated column. You can expand the SWITCH functions in variables vStartDatetimeAdj and vEndDatetimeAdj to handle additional scenarios (e.g., midnight).

 

Workday Seconds = 
VAR vWorkdayStartHour = 8
VAR vWorkdayEndHour = 17
VAR vColumn1 = bw_Cofiwerkorders[Date_time required]
VAR vColumn2 = bw_Cofiwerkorders[Date_time finished]
VAR vWorkdayHours = vWorkdayEndHour - vWorkdayStartHour
// the earlier of the two columns
VAR vStartDatetime = MIN ( vColumn1, vColumn2 ) 
// the later of the two columns
VAR vEndDatetime = MAX ( vColumn1, vColumn2 )
VAR vStartDate = INT ( vStartDatetime )
VAR vEndDate = INT ( vEndDatetime )
// count the workdays between StartDate and EndDate (non-inclusive)
VAR vWorkdaysBetween =
    CALCULATE (
        COUNTROWS ( DimDate ),
        DimDate[Date] > vStartDate,
        DimDate[Date] < vEndDate,
        DimDate[Weekday Flag] = 1
    )
// total workday seconds between StartDate and EndDate (non-inclusive)
VAR vWorkdaySecondsBetween = vWorkdaysBetween * vWorkdayHours * 60 * 60
// Start Date beginning of day
VAR vStartDateBOD = vStartDate + TIME ( vWorkdayStartHour, 0, 0 )
// Start Date end of day
VAR vStartDateEOD = vStartDate + TIME ( vWorkdayEndHour, 0, 0 )
// End Date beginning of day
VAR vEndDateBOD = vEndDate + TIME ( vWorkdayStartHour, 0, 0 )
// End Date end of day
VAR vEndDateEOD = vEndDate + TIME ( vWorkdayEndHour, 0, 0 )
VAR vStartDatetimeAdj = 
    SWITCH ( TRUE (),
        vStartDatetime < vStartDateBOD, vStartDateBOD,
        vStartDatetime > vStartDateEOD, vStartDateEOD,
        vStartDatetime
    )
VAR vEndDatetimeAdj = 
    SWITCH ( TRUE (),
        vEndDatetime < vEndDateBOD, vEndDateBOD,
        vEndDatetime > vEndDateEOD, vEndDateEOD,
        vEndDatetime
    )
VAR vStartDateSeconds = DATEDIFF ( vStartDatetimeAdj, vStartDateEOD, SECOND )
VAR vEndDateSeconds = DATEDIFF ( vEndDateBOD, vEndDatetimeAdj, SECOND )
VAR vTotalWorkdaySeconds =
    SWITCH ( TRUE (),
        vStartDate = vEndDate, DATEDIFF ( vStartDatetimeAdj, vEndDatetimeAdj, SECOND ),
        vStartDateSeconds + vWorkdaySecondsBetween + vEndDateSeconds
    )
VAR vResult = IF ( vColumn1 < vColumn2, vTotalWorkdaySeconds, vTotalWorkdaySeconds * -1 )
RETURN
    vResult