Count hours between dates but exclude weekends (Sat/Sun)

[rpinxt]

This is the situation:

HourDiff is a calculated columns (so are DT101 and DT311):

HourDiff = DIVIDE(DATEDIFF(SC2[DT101],SC2[DT311],MINUTE),60)

Works fine but as you can see here it says 61 hours.

However 1 march was a Friday and DT311 was finished Monday 4 march.

We do not want to count the weekend, 2 and 3 March here obviously.

So Hour Diff should be 48 hours lower.

My calendar has day nr and day name like :

Now I want to adjust Hourdiff to exclude Week Day Nr 6 and 7. Or use < 6.

Or exclude Week Day Sun and Sat, I do not really mind how.

How would I change the calculated column HourDiff to only take the datediff of weekdays?

[rpinxt · Accepted solution]

Ok got my workaround to work.....

This seems to work :

In my table I made calculate columns for the start and end day as follows :

D1 = WEEKDAY(SC2[Posting Date],2)

D2 = WEEKDAY(SC2[Posting Date311],2)

Because Posting Date and Posting Date 311 were already fields in my data I did not need a MAX for it.

Now I have in my data for every line it weekday number.

My logic seems to work now.

But for sure this should be easier?? Its just comparing 2 dates, calculate the hours but leave out the saturday and the sunday.

[rpinxt · Accepted solution]

Ok got my workaround to work.....

This seems to work :

In my table I made calculate columns for the start and end day as follows :

D1 = WEEKDAY(SC2[Posting Date],2)

D2 = WEEKDAY(SC2[Posting Date311],2)

Because Posting Date and Posting Date 311 were already fields in my data I did not need a MAX for it.

Now I have in my data for every line it weekday number.

My logic seems to work now.

But for sure this should be easier?? Its just comparing 2 dates, calculate the hours but leave out the saturday and the sunday.

[v-yilong-msft]

Hi  @rpinxt ,

I think it's perfect that you're using this method to make it easier and meet your needs, so have you solved your problem so far? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

[rpinxt · Accepted solution]

@v-yilong-msft well I would think there were better solutions than mine.

But if there are no others I will mark that one as a solution.

In fact I think I made a better solution myself using a calculated column called weekendcorrection like:

WeekendCorrection =

VAR weekend = CALCULATE(COUNTROWS(dimDate),DATESBETWEEN(dimDate[Date],SC2[DT101],SC2[DT311]-1),dimDate[IsWorkingDay] = FALSE(),ALL(dimDate)

)

RETURN

IF(weekend > 1, 48, 0)

Therefore I made an extra field in my autocalendar for 'IsWorkingDay'
("IsWorkingDay", NOT WEEKDAY([Date]) IN {1,7})

This gives for every date if it is a weekend or not. This way I can count if there is a weekend between DT101 and DT311.

Then you can substract WeekendCorrection (being 0 or 48) from Hourdiff

[rpinxt]

Trying a workaround here but there should be an easier way I would think....however.

Made this in a test excel based on 1 line of data :

As you see this would work. I recognizes that a weekend had past and subtracts 48 hours.

But if I try to incorp this in my model with multiple lines of data :

As you see it does not see that it is a Friday and a Monday (5 and 1).

For sure it has to do with the MAX formula in the _StartDay and _EndDay variables because there are more lines of data now.

Does anybody know how I can avoid this??

Ps: still would think there should be better solution for this whole problem....

[ChiragGarg2512]

Try 

Calculate(DIVIDE(DATEDIFF(SC2[DT101],SC2[DT311],MINUTE),60), Weekday(`Date Column`, 2) <6)

This will calculate the number of hours while filtering the days that are Saturday(6) or Sunday(7).

[rpinxt]

Well @ChiragGarg2512 I wrapped it in MAX but nothing happens:

[rpinxt]

Thanks @ChiragGarg2512 but something is not quite alright:

DT101 and DT311 are also calculated colunns so I guess something is different then.

Should I wrap them in MAX ?