cancel
Showing results for
Did you mean:  ## Calculate Work Hours/Network Hours Exlcuding Weekends SIMPLIFIED

Hey DataFam,

Took me a while to sift through different ways to calculate working hours/network hours and this was the simplest implementation I made up myself.

Step 1

Create a calendar table called 'Dates'

Step 2

create calculated column in calendar table to tag workdays

`IsWorkDay = SWITCH(WEEKDAY(Dates[Date],2),6,0,7,0,1)`

Step 3

Create calculated column to add work hours to work days

`Work Hours = IF(Dates[IsWorkDay]=1,8,0)`

Step 4

create calculated column 'Work Hours' in your Table1 with the start and end DATETIMEs.

```Work Hours =
IF (
CALCULATE (
SUM ( Dates[Work Hours] ),
DATESBETWEEN (
Dates[Date],
Table1[Start Date],
Table1[End Date]
)
) = 8,
DATEDIFF ( Table1[Start Date], Table1[End Date], MINUTE ) / 60,
IF (
CALCULATE (
SUM ( Dates[Work Hours] ),
DATESBETWEEN (
Dates[Date],
Table1[Start Date],
Table1[End Date]
)
) >= 16,
CALCULATE (
SUM ( Dates[Work Hours] ),
DATESBETWEEN (
Dates[Date],
Table1[Start Date],
Table1[End Date]
)
) - 16
+ (
(
DATEDIFF ( TIMEVALUE ( Table1[Start Date] ), TIME ( 17, 00, 00 ), MINUTE )
+ DATEDIFF (
TIME ( 09, 00, 00 ),
TIMEVALUE ( Table1[End Date] ),
MINUTE
)
) / 60
)
)
)```

This worked swimmingly for me I hope it works for you as a simpler solution than some other ones that have been presented!

Best,

Matt

Solved: measure 2 different work hours time and excluding ... - Microsoft Power BI Community

Calculate work hours between two time/date stamps

Power BI DATEDIFF only working days, hours and so on… – Tomasz Poszytek

Solved: Calculating TAT of working hours - Microsoft Power BI Community

Solved: Working hours without non-working hours, no weeken... - Microsoft Power BI Community

1 ACCEPTED SOLUTION  SOLVED ABOVE! Let me know if you have any questions!

2 REPLIES 2 Regular Visitor

Hi,

This worked as a charm and was th easiet way as compared to all the solutions I have seen.

What I was hoping to do is it to exclude public holidays (Have a table called public holidays with dates in them

&

Where there is no [end date] it should take it as now (I have a table where the date/time for now is captured)

Where [end date] is lesser than [start date] it should consider it as 0 mins.

Any help will be apprecated  SOLVED ABOVE! Let me know if you have any questions! Announcements #### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world. #### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st! #### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better. Top Solution Authors
Top Kudoed Authors
Users online (1,897)