1 2 Previous Next 18 Replies Latest reply on Mar 26, 2018 5:29 AM by Jan Brooijmans

    Date Differnce - only working hours - only working days

    aamir.zamir.0

      I am stuck in issue i need help
      I want to count hours difference between two dates on two cases
      1) count hours for only working hours (8am-5:30 pm)

       

      2) exclude weekdays (Saturday, Sunday )

       

      For example if an order is placed at 10/2/2013 5:28 p.m. on Friday and filled on 10/5/2013 8:10 a.m. on Monday.

       

      Then time difference will be 12 minutes.

      Jive Monitoring

      Kelly OsterhoutJulia RyanShawn WallworkDimitri Blyumin

        • 2. Re: Date Differnce - only working hours - only working days
          Dimitri.B

          Nobody said this is going to be easy, so you have been warned.

          Tableau doesn't have any built-in logic for these kind of calculations, so it had to be hacked. Use "formulae" below and replace field names.

           

          Option 1: Expressing number of working days between two dates as a fraction (decimal) of 9.5-hour working day, between 08:00 and 17:30.

           

           

          //work days on the day of Start Date
          IF 1 < DATEPART('weekday',[Start Date]) AND DATEPART('weekday',[Start Date]) < 7 //only for weekdays
              AND ([Start Date] - DATETRUNC('day',[Start Date])) < 1/24*17.5  //and only for events starting before work end
              AND DATETRUNC('day',[Start Date]) < DATETRUNC('day',[End Date]) //ignore same-day events, they are counted with End Date
          THEN
              //work days elapsed since Start Date (or 08:00, whchever is later) to work end time (17:30), as fraction of 9.5 hour work day
              ((DATETRUNC('day',[Start Date]) + 1/24*17.5) - MAX([Start Date],DATETRUNC('day',[Start Date]) + 1/24*8))/(1/24*(17.5-8))
          ELSE 0 //zero for weekends
          END
          +
          //number of *whole* (24 hours) work days between two dates
          //working days from Start Date to the end of that week
          IF DATEPART('weekday',[Start Date]) == 7 //0 for Sat
              //0 for start and end on the same week, to avoid double-counting, 
              //these days will be counted with End Date
              OR DATETRUNC('week',[Start Date]) == DATETRUNC('week',[End Date]) 
          THEN 0
          ELSE 7 - DATEPART('weekday',[Start Date]) - 1
          END
          +
          //work days from whole weeks between
          CASE SIGN(DATETRUNC('week',[End Date]) - (DATETRUNC('week',[Start Date])+7))
          WHEN -1 THEN 0 //set to 0 if whole weeks between is negative
          ELSE INT((DATETRUNC('week',[End Date]) - (DATETRUNC('week',[Start Date])+7))/7)*5
          END
          +
          //working days from beggining of that week to the End Date
          IF DATEPART('weekday',[End Date]) == 1 OR DATETRUNC('day',[Start Date]) == DATETRUNC('day',[End Date])
          THEN 0 //set 0 for Sundays and same-day start/end
          //number of days
          //from: either Monday, or Start Date if it is later than Monday
          //to: either End Date, or Saturday (excluding) whichever is earlier (toexclude Sat)
          ELSE INT(MIN(DATETRUNC('day',[End Date]),DATETRUNC('week',[End Date])+6) - MAX(DATETRUNC('week',[End Date])+1,DATETRUNC('day',[Start Date])+1))
          END
          +
          //work days on the day of End Date
          IF 1 < DATEPART('weekday',[End Date]) AND DATEPART('weekday',[End Date]) < 7 //only for weekdays
              AND ([End Date] - DATETRUNC('day',[End Date])) > 1/24*8 THEN //and only for events ending after work start
              //work days elapsed from 8:00 until Start Date (or 17:30, whchever is earlier), as fraction of 9.5 hour work day
              (MIN([End Date],DATETRUNC('day',[End Date]) + 1/24*17.5) - (DATETRUNC('day',[End Date]) + 1/24*8)) /(1/24*(17.5-8))
          ELSE 0 //zero for weekends
          END
          

           

          Option 2: Expressing the same in your normal days, hours and minutes, i.e. 2:04:12 - two days, four hours and twelve minutes, where days are 9.5-hour working days, not 24 hour days. So 2:04:12 will convert to 19+4 hours and 12 minutes.

           

           

          //work days on the day of Start Date
          IF 1 < DATEPART('weekday',[Start Date]) AND DATEPART('weekday',[Start Date]) < 7 //only for weekdays
              AND ([Start Date] - DATETRUNC('day',[Start Date])) < 1/24*17.5  //and only for events starting before work end
              AND DATETRUNC('day',[Start Date]) < DATETRUNC('day',[End Date]) //ignore same-day events, they are counted with End Date
          THEN
              //work days elapsed since Start Date (or 08:00, whchever is later) to work end time (17:30)
              ((DATETRUNC('day',[Start Date]) + 1/24*17.5) - MAX([Start Date],DATETRUNC('day',[Start Date]) + 1/24*8))
          ELSE 0 //zero for weekends
          END
          +
          //number of *whole* (24 hours) work days between two dates
          //working days from Start Date to the end of that week
          IF DATEPART('weekday',[Start Date]) == 7 //0 for Sat
              //0 for start and end on the same week, to avoid double-counting, 
              //these days will be counted with End Date
              OR DATETRUNC('week',[Start Date]) == DATETRUNC('week',[End Date]) 
          THEN 0
          ELSE 7 - DATEPART('weekday',[Start Date]) - 1
          END
          +
          //work days from whole weeks between
          CASE SIGN(DATETRUNC('week',[End Date]) - (DATETRUNC('week',[Start Date])+7))
          WHEN -1 THEN 0 //set to 0 if whole weeks between is negative
          ELSE INT((DATETRUNC('week',[End Date]) - (DATETRUNC('week',[Start Date])+7))/7)*5
          END
          +
          //working days from beggining of that week to the End Date
          IF DATEPART('weekday',[End Date]) == 1 OR DATETRUNC('day',[Start Date]) == DATETRUNC('day',[End Date])
          THEN 0 //set 0 for Sundays and same-day start/end
          //number of days
          //from: either Monday, or Start Date if it is later than Monday
          //to: either End Date, or Saturday (excluding) whichever is earlier (toexclude Sat)
          ELSE INT(MIN(DATETRUNC('day',[End Date]),DATETRUNC('week',[End Date])+6) - MAX(DATETRUNC('week',[End Date])+1,DATETRUNC('day',[Start Date])+1))
          END
          +
          //work days on the day of End Date
          IF 1 < DATEPART('weekday',[End Date]) AND DATEPART('weekday',[End Date]) < 7 //only for weekdays
              AND ([End Date] - DATETRUNC('day',[End Date])) > 1/24*8 THEN //and only for events ending after work start
              //work days elapsed from 8:00 until Start Date (or 17:30, whchever is earlier)
              (MIN([End Date],DATETRUNC('day',[End Date]) + 1/24*17.5) - (DATETRUNC('day',[End Date]) + 1/24*8))
          ELSE 0 //zero for weekends
          END
          + 1 //add one day to make Tableau's d:hh:mm display correctly
          

           

          Enjoy

          • 4. Re: Date Differnce - only working hours - only working days
            Matt Lutton

            Amazing is correct.  The fact that people take the time to answer (seemingly, to me, anyway) complex questions like this is awesome.

            • 6. Re: Date Differnce - only working hours - only working days
              aamir.zamir.0

              Great Thanks Dimitri.......

              • 8. Re: Date Differnce - only working hours - only working days
                Dimitri.B

                I had a look at the samples, but I don't understand what the question is.

                • 9. Re: Date Differnce - only working hours - only working days
                  aamir.zamir.0

                  actually here is problem
                  if it is same day and not Saturday/Sunday then count hours between same date with no condition
                  like :  start date                  end date                          hour  difference

                  fri 2013-01-01 4:30 pm        fri 2013-01-01 8:30 pm        4 hours

                   

                  if it is not for same days then count only working hour difference , working day (8am to 5.30 pm)

                   

                  like :  start date                  end date                            hour  difference

                  thu 2013-01-01 4:30 pm       fri 2013-01-01 12:30 pm               5 hour 30 minutes
                  only difference for working hours

                   

                  third case is like if end date comes in weekend then simply ignore weekend and count only working hours of working day

                   

                  like :  start date                  end date                           hour  difference

                  fri 2013-01-01 4:30 pm        Sat 2013-01-01 8:30 pm         1 hours

                   

                  (only work hour from 4:30 to 5:30 one hour , ignore weekend)

                   

                  yes dimitri i am in this tough scenario Dimitri Blyumin 

                  • 10. Re: Date Differnce - only working hours - only working days
                    Shawn Wallwork

                    Holy sh--! D that's some fancy dancin'.

                     

                    --Shawn

                    • 11. Re: Date Differnce - only working hours - only working days
                      Mark ODonnell

                      Dimitri Blyumin  I found this very useful, but would like to expand it and create another view with Holidays excluded.  Any ideas?  I am currently using networkdays in excel to do the same.

                       

                      I have researched this network days thread, but it appears your script already covers all scenarios except holidays and I have a separate data file with holiday dates.

                      Thanks....

                      • 12. Re: Date Differnce - only working hours - only working days
                        Dimitri.B

                        One way to do it would be to hard-code the public holiday dates right into the calculation. Another way is to have a reference table with all the holiday dates and do a data blend (your mileage may vary, data blends impose certain constraints the contents of the sheet). Yet another is to incorporate such reference table in the original source and join it to have an extra column with a flag marking holiday records.

                        • 13. Re: Date Differnce - only working hours - only working days
                          Maciek La

                          WHOA - this solution saved me a lot of time - thanks for that.

                          Dimitri Blyumin: I am struggling right now with holidays though could You please give me some tips how to incorporate the 'hard coded holidays' approach to Your solution?

                          • 14. Re: Re: Date Differnce - only working hours - only working days
                            Dimitri.B

                            You can add a snippet of code for each date of public holidays before or after the main block. It will look something like this:

                             

                            IF [Start Date] <= [your holiday date 1] AND [your holiday date 1] <= [End Date]
                                THEN -1 //deduct one day (or fraction of a day, as required)
                            ELSE 0 // nothing to deduct or add
                            END
                            +
                            IF [Start Date] <= [your holiday date 2] AND [your holiday date 2] <= [End Date]
                                THEN -1 //deduct one day (or fraction of a day, as required)
                            ELSE 0 // nothing to deduct or add
                            END
                            //...etc.
                            

                             

                            The potential problem would be if a public holiday falls on a weekend, in which case the code will overcompensate for it, but this can be avoided by checking for that and not including such occasions.

                            1 2 Previous Next