Forum Discussion

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
2 years ago
Solved

Count 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 ...
Need Help
  • rpinxt's avatar
    rpinxt
    2 years ago

    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's avatar
    rpinxt
    2 years ago

    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)
    )
    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's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
2 years ago

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....