1 2 Previous Next 15 Replies Latest reply on Aug 8, 2016 10:06 AM by chandra shekhar banerjee

    DateDiff for Working days only

    sinadashtipour

      Hi there,

       

      As the title reads, is there a function to work out the date differences which excludes weekends?

       

      I'm not sure if DateDiff does this.

       

      Thanks

        • 1. Re: DateDiff for Working days only
          Alex Kerin

          Here's a thread with some solutions:

           

          http://community.tableau.com/thread/105239

          1 of 1 people found this helpful
          • 2. Re: DateDiff for Working days only
            Alex Kerin

            There is also a calculation I have created - I'm sure it could be made more simple with some clever thought, but given a [date] and [start date], we first work out the day of the week of these:

             

            [Weekday Start]  datepart('weekday',[Start Date])

            [Weekday End]  datepart('weekday',[Date])

             

            First the whole weeks we must add (whole weeks times 5)

             

            int(([Date]-[Start Date]-1)/7)*5

             

            plus the extra days, wholly dependent on the day of week of start and end:

             

            case [Weekday End] when 1 then

                 if [Weekday Start]>5 then 7-[Weekday Start]

                 elseif [Weekday Start]=5 or [Weekday Start]=4 then 2

                 else 6-[Weekday Start] end

            When 2 then

                if [Weekday Start]=7 or [Weekday Start]=1 then 1

                elseif [Weekday Start]=5 or [Weekday Start]=6 then 2

                else 7-[Weekday Start] end

            When 3 then

                if [Weekday Start]>=3 and [Weekday Start]<=5 then 8-[Weekday Start]

                else 2 end

            When 4 then

                if [Weekday Start]=4 or [Weekday Start]=5 then 9-[Weekday Start]

                elseif [Weekday Start]=3 then 2

                else 3 end

            When 5 then

                if [Weekday Start]=4 or [Weekday Start]=3 then 6-[Weekday Start]

                elseif [Weekday Start]=5 then 5

                else 4 end

            When 6 then

                if [Weekday Start]=5 or [Weekday Start]=4 then 7-[Weekday Start]

                elseif [Weekday Start]=2 or [Weekday Start]=3 then 4

                else 5 end

            When 7 then

                if [Weekday Start]=6 or [Weekday Start]=5 then 7-[Weekday Start]

                elseif [Weekday Start]=3 or [Weekday Start]=4 then 3

                elseif [Weekday Start]=2 then 4

                else 5 end

            end

             

            This gives exactly the same answers as networkdays in Excel, but with no possibility of adding holidays as well.

            • 3. Re: DateDiff for Working days only
              sinadashtipour

              Thanks. I found this working for me:

               

              DATEDIFF("weekday",[Calculation1],[Calculation2])-2*(DATEPART('week', [Calculation2]) -DATEPART('week', [Calculation1]))

              • 4. Re: DateDiff for Working days only
                Alex Kerin

                Are you sure that calc is correct? I don't think it works properly, and doesn't work across years/jan-dec split

                • 5. Re: DateDiff for Working days only
                  sinadashtipour

                  Yes it seems to be ok. The only problem it seems to have when using dates from two different data sources (blended data). There seems to be so much restrictions when it comes to blending data. Is there a way around this problem?

                   

                  Thansk

                  • 6. Re: DateDiff for Working days only
                    Brett Raven

                    Hi I don't think this works perfectly yet. If you have the start and end date the same, the networkdays = 5. Needs some tweaking

                     

                    Has anyone added/seen a larger calculation that takes this into account along with the times (minutes preferably) with a lookup to a holiday table or data set?

                    • 7. Re: DateDiff for Working days only
                      Lynn Snow

                      Alex,

                       

                      Thanks for your post on 5/15/12 with the calculation to count workdays between two dates.  Most calculations I've found use DateDiff then subtract weekends, but those aren't accurate when weekends span between two months.  Yours was the most accurate calculation I've found so far, but it was slightly off, maybe because I was using it to calculate work days from the start to a midpoint in the month instead of for the entire month.

                       

                      Using your calculations as a starting point, I created a new method that worked exactly for me, so I wanted to post it in case it can help anyone else.  I included comments below with "//" to help explain how it works.

                       

                      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

                      2 of 2 people found this helpful
                      • 8. Re: DateDiff for Working days only
                        Alex Kerin

                        Nice job Lynn.

                        • 9. Re: DateDiff for Working days only
                          manish mishra

                          Hi Intellectuals,

                               Before going through solution I tried to make understand the requirements and solution approach.Please make me aware if found any flaws in my solution(even for specific cases).

                           

                          What's the requirement/challenges n caution to take=

                           

                          • We need to find business hour of an employee
                          • For holiday calculation-custom sql required (since blending or joining can’t performed)
                          • Working hour of an employee is 9AM-6pm and sat-sun are taken as weekends here
                          • Business days=total days elapsed in date range - all weekends - holidays in that date range+ all those holidays which falls on sat or sun
                          • So,exclude sat/sun from holidays becz all weekends taken out already

                           

                          • Actual holidays=[all holidays in date range] - [Exclude holidays which falls on weekends]
                          • Calculating missing hr means Hr. difference calculation corresponding to each start time and end time  as office is b/w 9-6
                          • Business hrs means all working hours of days(exclude net holidays n weekend)
                          • Business hours= [(total hrs elapsed - missing hrs)]

                           

                          • There is a list of holidays(although I've given this only for year 2012,You can add more dates of other years)
                          • Must check on aggregation (ie. sum,avg,min etc.) when taking measures in the view by dragging(why because records are kept in that way,view data to visualize this situation).
                          • All calculated field have given comments for better understanding(it'll tell u how it works)

                           

                           

                           

                          So,APPROACH to accomplish tasks is=====

                           

                          1>custom sql required to relate both sheets. 

                          2>calculation of  weekdays = days without weekends (take avg aggregation when dragging this measure)

                          3>holiday calculation in given date range

                          4>exclude sat/sun from holidays becz all weekends taken out already

                          5>find actual holidays

                          6>calculation of business days

                          7>calculation of missing hours (take min aggregation when dragging this measure)

                          8>calculation of business hours

                          9>Utilization calculation


                          See TWBX file for details.



                          my solution can also be referred on this thread

                          http://community.tableau.com/message/322255#322255

                           

                          It's works very perfect for all cases/conditions



                          • 10. Re: DateDiff for Working days only
                            Łukasz Majewski

                            Manish,

                             

                            Your calculation in the first row says 93 as "business hours" - I reckon this should be 84:

                             

                            simple check in excel:

                             

                            My attached solution results:

                            Here is explanation: Re: How to Calculate Working Minutes- Excluding Weekends and Holidays

                             

                            # I set [Sunday] field to 6 instead of 7 by mistake - corrected and republished now

                            • 11. Re: DateDiff for Working days only
                              manish mishra

                              Well Lukasz,

                              I've seen your solution,very complicated fields were written although useful and highly appreciable because I may ned to use the similar formulae in my other requirements.

                              Anyways,this solution is very useful but in context of my raised ticket I found it not solving  my requirement exactly.

                               

                              Few things I want to have clarified from your side.

                              1)As you said

                              "Your calculation in the first row says 93 as "business hours" - I reckon this should be 84:"

                              In order to justifying what I wrote please check 1st row manually in calender (forget calculated fields for a while)-

                              Since date range is too small that you can check this from calender.(Involve 26/3 and 9/4 also)

                              See Ashwin is working from 26 march to 9 april-2014.So,it's clear excluding sat/sun it's exactly 11 working days.

                              So,11 days X 9 hrs=99 hrs (since employee works 9hrs a day ie. 9AM-6PM that is fixed)


                              Now,consider arrival and departure time while calculating how much hr. he missed

                              as start time is 3PM(ie. 1500 hrs) so,he missed 6 hrs office (as he supposed to come at 9AM)


                              and end time 1AM,it doesn't make any difference(since leaving 1 at night is not taken in calculation as all time before 9AM and after 6PM for arriving and leaving office is not counted)


                              So,Ashwin in totality missed 6 hrs.


                              So,Business hr of Ashwin=99-6=93hrs.


                              2)Now,in your calculation let's take eg. of employee Suman

                              see only 1 day holiday comes in his tenure,that's fine,agreed.

                              So, accordingly corresponding "holiday time" would be 1day x 24 x 60 x 60=86400 seconds

                              but,in that place 32400 is computed as result in your twbx.


                              3)So,please clarify how holiday time and work time is calculated means how it can be associatted with requirement.(brief the approach)

                              4)Also,tell you made solution dynamic by supplying parameters

                              what's purpose of parameters sunend,satend,sunstart etc.


                              • 12. Re: DateDiff for Working days only
                                Łukasz Majewski

                                1) No, it is 9 full business days and 1 with 3 hours, hence 9x9+3

                                 

                                2) It is subtraction of business hours during holiday from the work time calculated as if there was no holiday, hence 1x9x3600 = 32400 seconds

                                 

                                3) Refer to my original post that I linked

                                 

                                4) Those parameters come to play when there is business over weekend but with different start/end hours than on weekdays, e.g. you work 9-18 Mon-Fri, 10-17 Sat and 10-14 Sun

                                • 13. Re: DateDiff for Working days only
                                  Karthik Ezhilvannan

                                  Lynn,

                                  Even your logic needs slight change.

                                  When the start date and end date are exactly seven days apart but the value of int([end date]-[start date])/7=0, the logic falls.

                                  • 14. Re: DateDiff for Working days only
                                    Łukasz Majewski

                                    Since the topic re-emerged after more than a year and the problem still does not seem to be properly solved I want to share my solution. It is very similar to what I mentioned above but deals with dates (not datetime) and counts days (and not seconds).

                                     

                                    1. Attached is a t-sql function as I believe this should be calculated at the source, e.g.

                                    select a.d1, a.d2, a.NETWORKDAYS_1, dbo.NetworkDays(d1, d2, 6,7) calc1, a.NETWORKDAYS_2, dbo.NetworkDays(d1, d2, 5,6) calc2
                                    , MIN(dbo.NetworkDays(d1, d2, 6,7))-SUM(dbo.NetworkDays(b.date, b.date, 6, 7)) Business_Days
                                    , COUNT(b.date) h_cnt, min(b.date) min_h_date, max(b.date) max_h_date, min(b.name) min_h_name, max(b.name) max_h_name
                                    from NETDAYS a left join (select * from COMREF.dbo.public_holidays where ISO_COUNTRY_CODE_SHORT='PL') b
                                    ON a.d1<=b.date and a.d2>=b.date
                                    group by a.d1, a.d2, a.NETWORKDAYS_1, dbo.NetworkDays(d1, d2, 6,7) , a.NETWORKDAYS_2, dbo.NetworkDays(d1, d2, 5,6)
                                    

                                     

                                    2. A tableau equivalent of the row level function I created looks as follows:

                                    As its name suggests this is supposed to be equivalent of excel NETWORKDAYS.INTL and you can specify the weekend days with a parameter.

                                     

                                    3. In order to be able to exclude holidays we need a table with holiday dates which we will LEFT JOIN with our primary data. In this example I am using data from excel file but Tableau only allows custom sql or '<=' join operator against excel if we use 'legacy connection'...

                                    or

                                     

                                    with such prepared dataset let's introduce holidays count:

                                    and finally the aggregated measure:

                                     

                                    The results:

                                     

                                    I have not tested this extensively so do let me know if you find an error.

                                     

                                    EDIT: I realized that - due to the fact I used 8 as the value for 'none' weekend day in the parameters - the formulas would yield incorrect results if we used that value (8%7==1%7). So I modified it and republished. Also removed redundant start_of_week parameter from DATEPART function.

                                    1 2 Previous Next