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.