Forum Discussion
rpinxt
Solution Sage
Solution SageCount hours between dates but exclude weekends (Sat/Sun)
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 ...
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
ChiragGarg2512Solution Sage
Solution SageTry
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).
rpinxtThanks 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 ?
- rpinxt