Forum Discussion

hsparg008's avatar
hsparg008
Frequent Visitor
4 years ago
Solved

Finding Working Hours Between Two Dates

I'm trying to find the number of working hours between two dates. I have a start and end date. Right now, I'm finding the number of working days between the dates and then multiplying it by the hours in the work day, but this is resulting in some rounding error (i.e. 4.3 days is rounded to 4 days). Is there a way to get a fraction of a day or calculate the working hours outright?

 

Working Hours -- 1st Shift is Monday to Friday 8 hours/day

2nd Shift is Monday to Thursday 10 hours/day -- only certain entries have a second shift

 

Right now I'm finding the 1st shift working days and the 2nd shift working days separately and then adding them together.

 

I have some of the DAX used in calculated columns below for reference. Are there any suggestions?

 

1. Find the number of first shift working days for an entry. It's the same for second shift but <=4

VAR MyDates = 
ADDCOLUMNS(
GENERATESERIES([Status Start Time]+1, [Status End Time]),
"Day", WEEKDAY([Value],2) // 1= Monday and 7 = Sunday
)
RETURN
COUNTROWS(FILTER(MyDates,[Day] <= 5)) //<= 5 means the weekday is monday through friday
2. Add the 1st shift and 2nd shift days to find the total working hours
Past Month Hours Duration =
'Historical Tech Center Submissions'[First Shift Days Past Month]*8 + 'Historical Tech Center Submissions'[Second Shift Days Past Month]*10
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi hsparg008 ,

     

    A new DAX used to return the number of workdays between date range. It always returns whole number.

     

    NETWORKDAYS(<start_date>, <end_date>[, <weekend>, <holidays>])

     

    NETWORKDAYS function (DAX) - DAX | Microsoft Docs

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    hsparg008 

     

    If you turn a date/time into a number (say, you multiply it by 1.0), it'll give you a number. On such numbers you can carry out arithmetic to obtain durations in (fractional) days. Check this out:

     

    EVALUATE
var dt = convert("2020-12-10 14:20:12", DATETIME)
return
    {(1.0 * dt, dt)}

     

    Run this in DAX Studio as is.

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi hsparg008 ,

     

    A new DAX used to return the number of workdays between date range. It always returns whole number.

     

    NETWORKDAYS(<start_date>, <end_date>[, <weekend>, <holidays>])

     

    NETWORKDAYS function (DAX) - DAX | Microsoft Docs

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.