cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

## Calculate Working Hours excl Weekends & Holidays

Hi All,

I have the following travelling data for vehicles where I need to calculate actual travel hours excluding weekends and holidays.

Considering Standard workings hours from 8:00 AM to 6:00 PM.

Request to help in this..

 First Start Time Last Stop Time 01-Apr-2022 07:41:26 AM 31-May-2022 04:41:07 PM 01-Apr-2022 09:03:47 AM 31-May-2022 06:29:15 PM 01-Apr-2022 06:09:36 AM 31-May-2022 05:55:29 PM 04-Apr-2022 02:41:09 PM 31-May-2022 07:00:30 PM 01-Apr-2022 08:08:56 AM 31-May-2022 06:28:53 PM 07-Apr-2022 08:59:19 AM 31-May-2022 05:10:01 PM 01-Apr-2022 12:01:36 PM 31-May-2022 07:09:00 PM 01-Apr-2022 07:34:28 AM 31-May-2022 06:31:49 PM

1 ACCEPTED SOLUTION
Super User

Hi @Ali27
I realized that there was a small mistake in the code where I used WEEKNUM function instead of using WEEKDAY. Here is the sample file updated https://we.tl/t-YYeFOgERsG

I doubled check the numbers manually and found accurate and the total is also accurate. It is also considering late start and early stop (these can be omitted if not required)

``````Working Hours =
VAR Result =
SUMX (
WorkingHours,
VAR CurrentStartDate = WorkingHours[First Start Time]
VAR CurrentStopDate = WorkingHours[Last Stop Time]
VAR StandardStartDate = DATE ( YEAR ( CurrentStartDate ), MONTH ( CurrentStartDate ), DAY ( CurrentStartDate ) ) + TIME ( 8, 0, 0 )
VAR StandardEndDate = DATE ( YEAR ( CurrentStopDate ), MONTH ( CurrentStopDate ), DAY ( CurrentStopDate ) ) + TIME ( 18, 0, 0 )
VAR LateStartMinutes = IF ( CurrentStartDate > StandardStartDate, DATEDIFF ( StandardStartDate, CurrentStartDate, MINUTE ), 0 )
VAR EarlyStopMinutes = IF ( CurrentStopDate < StandardEndDate, DATEDIFF ( CurrentStopDate, StandardEndDate, MINUTE ), 0 )
VAR AllDays = CALENDAR ( CurrentStartDate, CurrentStopDate )
VAR AllWorkingDays = FILTER ( AllDays, NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } ) && NOT ( [Date] IN VALUES ( Holidays[Date] ) ) )
VAR WorkedMinutes = COUNTROWS ( AllWorkingDays ) * 600
VAR NetWorkedMinutes = WorkedMinutes - LateStartMinutes - EarlyStopMinutes
RETURN
NetWorkedMinutes
)
RETURN
FORMAT ( QUOTIENT ( Result, 60 ), "#,#" ) & " Hours & " & FORMAT ( MOD ( Result, 60 ), "00" ) & " Minutes"``````
13 REPLIES 13
Community Support

Hi @Ali27

Ok, then it is calculated according to 10 hours of working time, I will update my formula, you can refer to the following .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @Ali27

What are your working hours? 8 hours or 10 hours ? Also, is the holiday you're talking about your regular holiday or just for you ? Looking forward to your reply .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

Hi Ailsa,

Our working hours is 8 hrs but I am considering 2 hrs as overtime. So need to get result with 10 hrs.

And the holidays are not the regular holidays, they are just for that particular occassion.

Super User

Hi @Ali27
I realized that there was a small mistake in the code where I used WEEKNUM function instead of using WEEKDAY. Here is the sample file updated https://we.tl/t-YYeFOgERsG

I doubled check the numbers manually and found accurate and the total is also accurate. It is also considering late start and early stop (these can be omitted if not required)

``````Working Hours =
VAR Result =
SUMX (
WorkingHours,
VAR CurrentStartDate = WorkingHours[First Start Time]
VAR CurrentStopDate = WorkingHours[Last Stop Time]
VAR StandardStartDate = DATE ( YEAR ( CurrentStartDate ), MONTH ( CurrentStartDate ), DAY ( CurrentStartDate ) ) + TIME ( 8, 0, 0 )
VAR StandardEndDate = DATE ( YEAR ( CurrentStopDate ), MONTH ( CurrentStopDate ), DAY ( CurrentStopDate ) ) + TIME ( 18, 0, 0 )
VAR LateStartMinutes = IF ( CurrentStartDate > StandardStartDate, DATEDIFF ( StandardStartDate, CurrentStartDate, MINUTE ), 0 )
VAR EarlyStopMinutes = IF ( CurrentStopDate < StandardEndDate, DATEDIFF ( CurrentStopDate, StandardEndDate, MINUTE ), 0 )
VAR AllDays = CALENDAR ( CurrentStartDate, CurrentStopDate )
VAR AllWorkingDays = FILTER ( AllDays, NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } ) && NOT ( [Date] IN VALUES ( Holidays[Date] ) ) )
VAR WorkedMinutes = COUNTROWS ( AllWorkingDays ) * 600
VAR NetWorkedMinutes = WorkedMinutes - LateStartMinutes - EarlyStopMinutes
RETURN
NetWorkedMinutes
)
RETURN
FORMAT ( QUOTIENT ( Result, 60 ), "#,#" ) & " Hours & " & FORMAT ( MOD ( Result, 60 ), "00" ) & " Minutes"``````
Community Support

Hi @Ali27

Create a measure like this :

``````WorkDays =
VAR Calendar1 = CALENDAR(MAX('Table'[First Start Time]),MAX('Table'[Last Stop Time]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
VAR _hour1 = (COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])-5)*10
VAR _hour2 = if(hour(SELECTEDVALUE('Table'[Last Stop Time]))>=18,hour(SELECTEDVALUE('Table'[Last Stop Time])-SELECTEDVALUE('Table'[First Start Time]))-1,hour(SELECTEDVALUE('Table'[Last Stop Time])-SELECTEDVALUE('Table'[First Start Time])))
VAR _Minute=if(HOUR(SELECTEDVALUE('Table'[Last Stop Time]))>=18,60-MINUTE(SELECTEDVALUE('Table'[First Start Time])),MINUTE(SELECTEDVALUE('Table'[Last Stop Time])-SELECTEDVALUE('Table'[First Start Time])))
VAR _wholehour = _hour1+_hour2
VAR _diff = _wholehour& "h" &_Minute &"m"
RETURN _diff``````

You will get a result like this :

I have attached my pbix file , you can refer to it .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

Hi @v-yetao1-msft
Working hours are 10 not 8. I believe the the off days need to be considered automatically not manually. also the grand total in this case is of a great importance and cannot be just ignored.

Super User

Here is a sample file with the solution

As the holidays data is not available the following code consideres only weekends (Saturdays and Sundays). Also late start and early stop are also considred. All calculations are curried out over minutes level then converted to HH:MM format

``````Working Hours HH:MM =
VAR Result =
SUMX (
WorkingHours,
VAR CurrentStartDate = WorkingHours[First Start Time]
VAR CurrentStopDate = WorkingHours[Last Stop Time]
VAR StandardStartDate = DATE ( YEAR ( CurrentStartDate ), MONTH ( CurrentStartDate ), DAY ( CurrentStartDate ) ) + TIME ( 8, 0, 0 )
VAR StandardEndDate = DATE ( YEAR ( CurrentStopDate ), MONTH ( CurrentStopDate ), DAY ( CurrentStopDate ) ) + TIME ( 18, 0, 0 )
VAR LateStartMinutes = IF ( CurrentStartDate > StandardStartDate, DATEDIFF ( StandardStartDate, CurrentStartDate, MINUTE ), 0 )
VAR EarlyStopMinutes = IF ( CurrentStopDate < StandardEndDate, DATEDIFF ( CurrentStopDate, StandardEndDate, MINUTE ), 0 )
VAR AllDays = CALENDAR ( CurrentStartDate, CurrentStopDate )
VAR AllWorkingDays = FILTER ( AllDays, NOT ( WEEKNUM ( [Date], 2 ) IN { 6, 7 } ) )
VAR WorkedMinutes = COUNTROWS ( AllWorkingDays ) * 600
VAR NetWorkedMinutes = WorkedMinutes - LateStartMinutes - EarlyStopMinutes
RETURN
NetWorkedMinutes
)
RETURN
FORMAT ( QUOTIENT ( Result, 60 ), "#,#" ) & " Hours & " & FORMAT ( MOD ( Result, 60 ), "00" ) & " Minutes"``````
Super User

@Ali27
Where is the data for weekends and holidays?

Regular Visitor

hii

Weekends are on Saturday & Sunday

And holidays from

02/05/2022 Monday to 06/05/2022 Firday

Super User

@Ali27
Here is the updated sample file https://we.tl/t-IJwSL1qGc5

``````Working Hours =
VAR Result =
SUMX (
WorkingHours,
VAR CurrentStartDate = WorkingHours[First Start Time]
VAR CurrentStopDate = WorkingHours[Last Stop Time]
VAR StandardStartDate = DATE ( YEAR ( CurrentStartDate ), MONTH ( CurrentStartDate ), DAY ( CurrentStartDate ) ) + TIME ( 8, 0, 0 )
VAR StandardEndDate = DATE ( YEAR ( CurrentStopDate ), MONTH ( CurrentStopDate ), DAY ( CurrentStopDate ) ) + TIME ( 18, 0, 0 )
VAR LateStartMinutes = IF ( CurrentStartDate > StandardStartDate, DATEDIFF ( StandardStartDate, CurrentStartDate, MINUTE ), 0 )
VAR EarlyStopMinutes = IF ( CurrentStopDate < StandardEndDate, DATEDIFF ( CurrentStopDate, StandardEndDate, MINUTE ), 0 )
VAR AllDays = CALENDAR ( CurrentStartDate, CurrentStopDate )
VAR AllWorkingDays = FILTER ( AllDays, NOT ( WEEKNUM ( [Date], 2 ) IN { 6, 7 } ) && NOT ( [Date] IN VALUES ( Holidays[Date] ) ) )
VAR WorkedMinutes = COUNTROWS ( AllWorkingDays ) * 600
VAR NetWorkedMinutes = WorkedMinutes - LateStartMinutes - EarlyStopMinutes
RETURN
NetWorkedMinutes
)
RETURN
FORMAT ( QUOTIENT ( Result, 60 ), "#,#" ) & " Hours & " & FORMAT ( MOD ( Result, 60 ), "00" ) & " Minutes"``````

Super User

Here a similar thread. You can find various other links in there that I am sure will be helping you!

Working days between two dates - simply! - Microsoft Power BI Community

And here a video with a function in Power Query:

PowerBI: Calculate business/working days between two dates in a new column - YouTube

Let me know if this helps 🙂

 Did I answer your question❓➡️ Please, mark my post as a solution ✔️

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Resolver I

Thank you the video was a great help, im trying to create a column with the data from the querey now to work out how many working day each person had had off in total.

work days = CALCULATE(SUM('DaysOff (2)'[Days off work]),ALLEXCEPT('DaysOff','DaysOff (2)'[UniqueID]))+0

On the datediF column  that works out the calendar days off, the formula above adds up all of the days into one cell for me. I want both to be in a table the days off from a specific absence then the total days the person has taken off in total too in the same row.
eg.
B Boo = 3   12
B Boo = 6   12
B Boo = 3   12
Resolver I

Probaly not the correct way to resolve this,excuse me as im new to PBI.

I made a help column out of the querey column using value to chnage the number to text, then used my formula and it worked. 🙂

work days = CALCULATE(SUM('DaysOff (2)'[Days off work]),ALLEXCEPT('DaysOff','DaysOff (2)'[UniqueID]))+0