Forum Discussion
Solution SageCount hours between dates but exclude weekends (Sat/Sun)
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.Anonymous 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))RETURNIF(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
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
Solution SageAnonymous 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:
("IsWorkingDay", NOT WEEKDAY([Date]) IN {1,7})