Calculate hours difference between two dates (excluding weekend)

[BigKev]

Hi All,

Does anyone know how I can calculate in dax the hours difference between two dates/ time columns (excluding weekend dates)?

I have created a (T/F) flag in my master calendar to flag if a date is a weekend or not. So it just a matter to join both of them together.

Please find below my data.

Thanks all.

Cheers,

Kev

Start Date	End Date
17/03/2021 17:30	18/03/2021 16:45
18/03/2021 17:00	20/03/2021 16:03
19/03/2021 17:00	22/03/2021 16:00
17/03/2021 10:00	18/03/2021 16:00
18/03/2021 17:00	20/03/2021 16:00
19/03/2021 17:33	22/03/2021 16:50
17/03/2021 8:00	17/03/2021 8:00
18/03/2021 15:00	20/03/2021 16:00
19/03/2021 17:00	22/03/2021 16:55
17/03/2021 17:00	18/03/2021 17:00
18/03/2021 17:00	20/03/2021 16:00
19/03/2021 17:00	22/03/2021 21:00

[v-luwang-msft · Accepted solution]

Hi @BigKev ,

You cold try  the following  Steps:

Step 1:create date table:

Dates = CALENDAR("2021/1/1","2021/12/31")

Step 2:create new table base on dates:

date1 =
GENERATE (
    CALENDARAUTO (),
    VAR yyyy =
        YEAR ( [Date] )
    VAR mmmm =
        MONTH ( [Date] )
    RETURN
        ROW (
            "Year", yyyy,
            "Month", FORMAT ( [Date], "mmmm" ),
            "Month2", mmmm,
            "Week1", FORMAT ( [Date], "dddd" ),
            "Week2", WEEKDAY ( [Date] ),
            "weekday", NOT WEEKDAY ( [Date] ) IN { 1, 7 }
        )
)

Step 3, Create new column in main database table:

difforder1 =

VAR day1 =

CALCULATE (

COUNTROWS ( 'date1' ),

DATESBETWEEN ( date1[Date], Abc[Start Date], Abc[End Date]),

date1[weekday] <> TRUE,

ALL ( Abc )

)

VAR day2 =

IF ( day1 = BLANK (), 0, day1 )

VAR day3 =

DATEDIFF ( Abc[Start Date], Abc[End Date], HOUR ) - day2 * 24

RETURN

day3

Wish it is helpful for you!

Best Regard

Lucien Wang

[BigKev]

Hi @amitchandak , 

Thanks for the prompt response.

I don't need to consider business hours. I just need to calculate the number of hours between two dates (as per my data above), excluding the weekend date.

For example,

Start Date                    End Date                        No. of Hours 

18/03/2021 8:00        19/03/2021 10:00                26

18/03/2021 8:00        22/03/2021 17:00                55

As you can see in row one, the two dates are working dates. Hence the hour's diff between the two dates is 26 hours.

In Row two, we don't consider 20/03 & 21/03 because it is Sat & Sun. Hence, the hour's diff is 55 hours.

do you know how I can calculate these hours diff?

Cheers,

Kev

[amitchandak]

@BigKev , If you need business hours

https://exceleratorbi.com.au/calculating-business-hours-using-dax/

ot try a measure like

Work hours =
var _dt1 = date(year(Table[Start Date]),month(Table[Start Date]),day(Table[Start Date]))
var _dt2 = date(year(Table[End Date]),month(Table[End Date]),day(Table[End Date]))
return
if(weekday(_dt1,2) <6 , datediff(Table[Start Date],_dt1+1,hours),0) + COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(_dt1+1,_dt2-1),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))*24
+ if( not(weekday(_dt2,2) in {6,7}) , datediff(dt_2 -1 , Table[End Date],hours),0)