1 Reply Latest reply on Oct 23, 2017 11:37 AM by patrick.byrne.0

    Calculating the difference between two days excluding weekends with days in decimals

    Vikram Kalimuthu Babu Satyakandhan

      Hello Everyone,

       

      I'm looking at creating a calculated field with the difference in timestamps between 2 days excluding weekends. Let's say A is 06-Oct-2017 08:35:00 and B is 12-Oct-2017 20:35:00 and I want to find the difference between the same. I've succeeded in figuring out the work days between the two days. So, if we had a weekend in between, this would show up as 4.00 instead of 6.00. However, I would like to include the hours also and show as 4.15 or 4.24 with the 24 hours being converted to decimals. So, in this case, since the difference is 4 workdays and 12 hours, it would be 4.50.

       

      Can someone help me with his formula?

       

      Thanks.

        • 1. Re: Calculating the difference between two days excluding weekends with days in decimals
          patrick.byrne.0

          Hello Vikram,

           

          To create the desired view some additional steps would need to be taken. As the ability to have the DATEDIFF formula return decimal values for the next part of the date is not currently built in to the function. I would suggest posting the Idea to the Tableau Community Idea: Ideas . This helps us evaluate the next features to add to the Tableau products.

           

          One way that you could accomplish the desired view, is to create two individual calculations, one that returns the days, the other that returns the hours. The DATEDIFF calculation will then need to be converted to a decimal. This can be accomplished by taking the integer value and dividing it by 24 in another calculation.

           

          Once both calculations are created and our hour has been converted to a decimal, we can combine the calculations to display the value as desired. Do not include the brackets below for this example.

           

          {

          SUM([DateDiff - day] ) + SUM ([DateDiff - hour])

           

          }

           

          Additionally, to remove weekends from the data, please review the following Tableau Community post.How to Exclude Weekends

           

          I hope this helps!

           

          Cheers,

          Byrne, Patrick