Forum Discussion
Calculate hours difference between two dates (excluding weekend)
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 |
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 * 24RETURNday3Wish it is helpful for you!
Best Regard
Lucien Wang
3 Replies
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 * 24RETURNday3Wish it is helpful for you!
Best Regard
Lucien Wang
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)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
