1 Reply Latest reply on May 9, 2018 1:28 PM by Austen Robinson

    How to calculate business minutes, excluding weekends, holidays and minutes out of range?

    Samuel Martins

      Hello Tableau Community,

       

      I need calculate the difference between two dates. But i have some rules: I can include in my calculation only the minutes that i work per day, that would be 480 minutes. The minutes remaning (before and after) in the business day, and of course weekends and holidays i have to eliminate. I already check a lot of solutions as: "how to eliminate weekends and holidays ?" or "how to calculate business days ?", it was good, but i could not solve my problem 100%. My date field format is: dd/mm/yyyy  hh:mm:ss. Here is my package workbook and excel file attached.

       

      FYI: Tableau version is 10.4

       

      Thanks in advance!!

        • 1. Re: How to calculate business minutes, excluding weekends, holidays and minutes out of range?
          Austen Robinson

          Substitute your fields to suite.

           

          WeekdayStart

          // Return day number in week (1=Sunday, 2=Monday, etc..., 7=Saturday)

          DatePart('weekday',[Start Date])

           

          WeekdayEnd

          // Return day number in week (1=Sunday, 2=Monday, etc..., 7=Saturday)

          DatePart('weekday',[End Date])

           

          Work Days

          // (1) Calculate number of whole weeks between dates, then convert to # of Work Days

              INT(([End Date]-[Start Date])/7)*5

          +

          // (2) If sum of the first and last week days is a full week, then it was accounted for above,

          //     so we need to subtract those work days because we will calculate work days for the

          //     first and last weeks in the next two steps

              IF

                  // # of total days in 1st Week

                  8-[WeekdayStart]

                  +

                  // # of total days in Last Week

                  [WeekdayEnd]

                  > 7

              // Subtract week of work days from step (1) which will be calculated in steps (3) & (4) below

              THEN -5

              ELSE 0

              END

          +

          // (3) Add # of Work Days for 1st Week

              // If 7 Days, then subtract 2 for Sun & Sat

              IF 8-[WeekdayStart] = 7 THEN 8-[WeekdayStart]-2

              // If 1 Day and it's Sunday, then exclude

              ELSEIF 8-[WeekdayStart] = 1 AND [WeekdayStart] = 1 THEN 0

              // Otherwise subtract the one Sunday

              ELSE 8-[WeekdayStart]-1

              END

          +

          // (4) Add # of Work Days for Last Week

              // If 7 Days, then subtract 2 for Sun & Sat

              IF [WeekdayEnd] = 7 THEN [WeekdayEnd]-2

              // If 1 Day and it's Sunday, then exclude

              ELSEIF [WeekdayEnd] = 1 THEN 0

              // Otherwise subtract the one Sunday

              ELSE [WeekdayEnd]-1

              END