cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MWinter225
Advocate IV
Advocate IV

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

 

 

FYI other links that were helpful on this subject:

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
MWinter225
Advocate IV
Advocate IV

SOLVED ABOVE! Let me know if you have any questions! 

View solution in original post

2 REPLIES 2
jolewis
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

 

MWinter225
Advocate IV
Advocate IV

SOLVED ABOVE! Let me know if you have any questions! 

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!