8 Replies Latest reply on Apr 1, 2018 11:04 AM by Wilford Bradford

    Hours calculation excluding weekends

    Daniel Benson

      Can anyone help me with calculating difference in hours excluding weekends?  Examples below.

       

         

      Start TimeEnd TimeHours Diff. (excluding Sat and Sun)Current calculation
      5/6/2016 9:00:00 AM5/10/2016 11:09:57 AM50.298.2
      5/6/2016 10:00:00 AM5/12/2016 10:24:15 AM96.4144.4

       

      Thanks,

        • 1. Re: Hours calculation excluding weekends
          Ben Young

          Hi Daniel,

           

          Excluding weekends is a popular topic on the forums. Here are a couple links to other threads which hopefully will help you out:

          Re: Exclude weekends and holidays

          Re: DateDiff for Working days only

          Calculate Working minutes taking into account Week days, bank holidays, and Business Hours

           

          And here's a link to a knowledge base article about using business days:

          Calculating the Number of Business Days | Tableau Software

           

          Hope this helps.

           

          --Ben

          • 2. Re: Hours calculation excluding weekends
            Daniel Benson

            Thanks, Ben.  I have looked over a lot of these.  I will go through them again to see if I can make one work.

            • 3. Re: Hours calculation excluding weekends
              Shawn Wallwork

              Will a Start Time or End Time ever fall on a weekend day?

               

              If not then this calc should work:

               

              DATEDIFF('hour', [Order Date], [Ship Date]) -

              (DATEDIFF('week', [Order Date], [Ship Date]) * 48)

               

              --Shawn

               

              EDIT: Except when your dates cross into a new year, then if fails.

              1 of 1 people found this helpful
              • 4. Re: Hours calculation excluding weekends
                Daniel Benson

                Shawn,

                Unfortunately I am see some that do fall on weekends.  But maybe I can put logic in to handle those.  Thanks for the help.

                • 5. Re: Hours calculation excluding weekends
                  Shawn Wallwork

                  That'll be an interesting calc! If the end time is 10:00 Saturday morning, will you be adding 10 hours (from midnight), or 2 hours (assuming a 8 a.m. start of the day) or 1 hour (assuming a 9 a.m. start of day).

                   

                  If you figure out all the possible permutations, post them and we can help you account for them all.

                   

                  --Shawn

                  • 6. Re: Hours calculation excluding weekends
                    Daniel Benson

                    Will do.  Thanks.

                    • 7. Re: Hours calculation excluding weekends
                      Corey Kennedy

                      We recently had an application for this and came up with a solution that excluded hours over the weekend. The formula has a few opportunities for improvement but it works across all possible start and end dates. It is a really long and ugly formula but at least it works! Notes: week diff is Datediff('week', [Created Dt],[Completed Dt]); Weekday Start is DATEPART('weekday',[Created Dt]); and Weekday End is DATEPART('weekday',Completed Dt). In order to get partial hours everything is evaluated at the minute level and then converted to hours. If you don't need to get this granular you could change everything to hours (just remember to take out the conversion logic).

                       

                      Hopefully this helps someone!

                       

                      -Corey

                       

                      // The first IF statement evaluates items that are created and completed on the same weekend.

                      // These items are set to 0 since the occurred exclusively over the weekend.

                      IF [week diff] = 1 and [Weekday Start]=7 and [Weekday End] = 1

                      then 0

                      ELSEIF [week diff] = 0 and

                      ([Weekday Start] = 1 or [Weekday Start] = 7)

                      and

                      ([Weekday End] = 1 or [Weekday End] = 7)

                      then 0

                      else

                      //If the above two conditions are not met we need to calculate how much time occurred of the weekend.

                      //For every week(second date diff) that elapses 48 hours are subtracted from total hours(first date diff).

                      DATEDIFF('minute',[Created Dt],[Completed Dt])/60

                      +

                      DATEDIFF('week',[Created Dt],[Completed Dt])*-48

                      +

                      //The following statements are adjustments to the above 48hour subtraction depending

                      //on what day the item is created or completed.

                      //If the item is created on Saturday we need to add back the time from 12am Saturday

                      //morning to its actual created time on Saturday.

                      IF  [week diff] >= 1 and [Weekday Start] = 7

                      then 24+(DATEDIFF('minute',DATETRUNC('day',[Created Dt]),[Created Dt])-1440)/60

                      else 0

                      END

                      +

                      //If the item is completed on a Sunday we need to add back in the time from when the ticket

                      // completed Sunday to the end of the day Sunday.

                      IF [week diff] >=1 and [Weekday End] = 1

                      then 24-(DATEDIFF('minute',DATETRUNC('day',[Completed Dt]),[Completed Dt]))/60

                      else 0

                      END

                      +

                      // The following statements subtract weekend time if the item did not last more than 1 week

                      // If the item was completed on Saturday we need to subtract out them time from 12am Saturday

                      // until the time the item was completed.

                      IF [week diff] = 0 and [Weekday End] =7

                      then -(DATEDIFF('minute',DATETRUNC('day',[Completed Dt]),[Completed Dt]))/60

                      else 0

                      END

                      +

                      // If the ticket was created on Sunday we need to subtract out the time from when the item was created

                      // and the end of day Sunday.

                      IF [week diff] = 0 and [Weekday Start] = 1

                      then (DATEDIFF('minute',DATETRUNC('day',[Created Dt]),[Created Dt])-1440)/60

                      else

                      0

                      END

                      END

                      1 of 1 people found this helpful
                      • 8. Re: Hours calculation excluding weekends
                        Wilford Bradford

                        Corey you came through in the clutch on this one.  I used your formula and although complex, seems to be working.  This probably took some time to think about the logic for it.

                         

                        The only thing I can think about that might not be accounted for is holidays which wasn't asked in the op.  I think it would be fairly easy to make a calculated field based off of a data source with the holiday dates and then add it into your initial IF statement.