2 Replies Latest reply on Jan 15, 2018 10:17 AM by Luke Brady

    Number of weekend days between two dates (or business days)

    Jason Scarlett

      After much searching and seeing various solutions, I think this solution may be the simplest.

      The basic idea is to:

      1. Calculate the number of weeks that overlap a Saturday
        (number of days + bonus week)/7
      2. Double this count
      3. Add lone Sunday
      4. Subtract lone Sunday

       

      Getting "business days" from this is straight forward .. next up holidays

        • 1. Re: Number of weekend days between two dates (or business days)
          Jason Scarlett

          // CALCULATE THE NUMBER OF SATURDAY'S AND SUNDAY'S BETWEEN TWO DATES (INCLUSIVE)

          // Count the number of Saturday's

          // (hint, the closer you get to Saturday, the more likely we are to add another week)

          int( (DATEDIFF('day',[Start Dt],[End Dt])+DATEPART('weekday',[Start Dt])) /7 )

          // Double the Saturday count (will need to fix if there is only one Saturday or Sunday)

          *2

          // Now deal with single day weekend overlaps that were missed or over compensated for

          // Add 1 because we have one more Sunday than we thought

          // (i.e. no Saturday, but there is a Sunday)

          + (if DATEPART('weekday',[Start Dt]) = 1 then 1 else 0 end)

          // Subtract 1 because we have one less Sunday than we thought

          // (i.e. period ended on Saturday, but we added an extra Sunday count above)

          - (if DATEPART('weekday',[End Dt]) = 7 then 1 else 0 end)

           

          1 of 1 people found this helpful
          • 2. Re: Number of weekend days between two dates (or business days)
            Luke Brady

            Thank you!  Exactly what I was looking for!