Calculate Working Hours excl Weekends & Holidays

[Ali27]

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

[v-yetao1-msft]

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.

[v-yetao1-msft]

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.

[Ali27]

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. 

[tamerj1 · Accepted solution]

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"

[v-yetao1-msft]

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.

[tamerj1]

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.

[tamerj1]

 @Ali27

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"

[tamerj1]

@Ali27 
Where is the data for weekends and holidays?

[Ali27]

hii

Weekends are on Saturday & Sunday

And holidays from

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

[tamerj1]

@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"

[tackytechtom]

Hi @Anonymous@Ali27 ,

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 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

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

[Anonymous]

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