topic Finding Working Hours Between Two Dates in DAX Commands and Tips

Finding Working Hours Between Two Dates

hsparg008 — Thu, 18 Aug 2022 22:09:45 GMT

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

Re: Finding Working Hours Between Two Dates

daXtreme — Thu, 18 Aug 2022 23:43:38 GMT

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.

Re: Finding Working Hours Between Two Dates

v-chenwuz-msft — Tue, 23 Aug 2022 07:50:42 GMT

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.