cancel
Showing results for 
Search instead for 
Did you mean: 
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
Community Champion
Community Champion

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
Ailsa-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.

Ailsa-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
Community Champion
Community Champion

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"
Ailsa-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.

tamerj1
Community Champion
Community Champion

Hi @Ailsa-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
Community Champion
Community Champion

 @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
Community Champion
Community Champion

@Ali27 
Where is the data for weekends and holidays?

Ali27
Regular Visitor

hii

 

Weekends are on Saturday & Sunday

 

And holidays from

 

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

 

 

tamerj1
Community Champion
Community Champion

@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

tomfox
Community Champion
Community Champion

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/

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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors