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

 

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

7 Replies

  • rpinxt's avatar
    rpinxt
    Icon for Solution Sage rankSolution Sage
    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

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

      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 ?

       

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