Calculate Work Hours/Network Hours Exlcuding Weekends SIMPLIFIED

[MWinter225]

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

[jolewis]

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 · Accepted solution]

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