Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
10 years ago
Solved

Excluding/Filtering by Certain times

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)   Example: if dateIN i...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    10 years ago

    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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    10 years ago

    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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
10 years ago

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's avatar
    bhmiller89
    Helper V
    10 years ago

    The end result isn't correct: 

     

    For this one, it's showing -0.46 hours which would be 27 minutes of "business hours."  1. why is it negative? and 2. Since the request came in after 8pm and was sent at 9:20am, the value should be around 0.33

     

    I feel like it's so close though! 

     

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      10 years ago

      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

  • powerbi1986's avatar
    powerbi1986
    Frequent Visitor
    3 years ago

    what is 9 and 18 ya?

  • powerbi1986's avatar
    powerbi1986
    Frequent Visitor
    3 years ago

    what is 9 and 18 ya

    how to set 830am - 5.30pm ?

     

    counting business hours (0830am-5.30pm)