Excluding/Filtering by Certain times

bhmiller89

I have two columns, "dateIN" and "dateDelivered" that are date/time types. I need to calculate the number of hours between the two, but only counting business hours (9am-6pm)

---

Using the formula below(just change "MyTable" to your table name) to create a calculate column should meet your requirement:

TotalWorkHour =
VAR Days =
    DATEDIFF ( MyTable[dateIN], MyTable[dateDelivered], DAY )
VAR InHour =
    HOUR ( MyTable[dateIN] )
VAR DeliveredHour =
    HOUR ( MyTable[dateDelivered] )
RETURN
    IF ( InHour < 9, 9, IF ( InHour > 18, 0, 18 - InHour ) )
        + IF ( DeliveredHour < 9, 0, IF ( DeliveredHour > 18, 9, DeliveredHour - 9 ) )
        + ( Days - 1 )
        * 9

Regards

bhmiller89

Please refer to the formula below:

TotalWorkHour = 
VAR Days =
    DATEDIFF ( MyTable[dateIN], MyTable[dateDelivered], DAY )
VAR InHour =
    HOUR ( MyTable[dateIN] ) + MINUTE(MyTable[dateIN])/60
VAR DeliveredHour =
    HOUR ( MyTable[dateDelivered] ) + MINUTE(MyTable[dateDelivered])/60
RETURN
    IF ( InHour < 9, 9, IF ( InHour > 18, 0, 18 - InHour ) )
        + IF ( DeliveredHour < 9, 0, IF ( DeliveredHour > 18, 9, DeliveredHour - 9 ) )
        + ( Days - 1 )
        * 9

Regards