Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Ali27
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 TimeLast Stop Time
01-Apr-2022 07:41:26 AM31-May-2022 04:41:07 PM
01-Apr-2022 09:03:47 AM31-May-2022 06:29:15 PM
01-Apr-2022 06:09:36 AM31-May-2022 05:55:29 PM
04-Apr-2022 02:41:09 PM31-May-2022 07:00:30 PM
01-Apr-2022 08:08:56 AM31-May-2022 06:28:53 PM
07-Apr-2022 08:59:19 AM31-May-2022 05:10:01 PM
01-Apr-2022 12:01:36 PM31-May-2022 07:09:00 PM
01-Apr-2022 07:34:28 AM31-May-2022 06:31:49 PM

 

1 ACCEPTED SOLUTION
tamerj1
Super User
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)

5.png

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"

View solution in original post

13 REPLIES 13
v-yetao1-msft
Community Support
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.

v-yetao1-msft
Community Support
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.

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
Super User
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)

5.png

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
Community Support
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 :

Ailsamsft_0-1657012311001.png

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.

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
Super User
Super User

 @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

1.png

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
Super User
Super User

@Ali27 
Where is the data for weekends and holidays?

hii

 

Weekends are on Saturday & Sunday

 

And holidays from

 

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

 

 

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

1.png

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"

2.png3.png

tackytechtom
Super User
Super User

Hi @Blondyvl@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/



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

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors