4 Replies Latest reply on Oct 23, 2014 3:58 PM by kettan

    How to determine if a certain date falls within a date range

    Mark Riley

      Hi all,

       

      I've only been using Tableau for about a week so I'm very new, I'd like to ask a question if I may, I've searched for an answer but I couldn't find anything that fills the requirement.

       

      I'm looking to identify if a list of dates (holidays such as Christmas day and bank holidays in the UK) fall within between two dates, so far I've put together the calculated field below in order to determine how many working days are between todays date and the target due date. I now need a way to exclude holidays from the result of this calculation,

       

      e.g. There are 10 working days between todays date and the target due date, however 1 day is a public holiday, therefore the number of working days should be 9.

       

      DATEDIFF('week',today(), [TARGET DUE DATE], 'sunday')*5

      + MIN(DATEPART('weekday', [TARGET DUE DATE]),6)

      - MIN(DATEPART('weekday',today()),5)

       

      I've made a list of all the holday dates in excel and connected the Tableau workbook to it, I had thought that I might be able to use that somehow. Is what I'm trying to do possible?

       

      Thank you for reading

       

      Mark (UK)

        • 1. Re: How to determine if a certain date falls within a date range
          Shawn Wallwork

          Mark welcome to the forums! You're on the right track, and yes the Excel sheet of holidays will be helpful. Read this thread by Catherine Rivier : Re: Include Date value of Null when prior business day date is selected

           

          That should get you going.

           

          --Shawn

          • 2. Re: How to determine if a certain date falls within a date range
            kettan

            Welcome to the forum, Mark!

             

            I believe you get more exact answers by attaching a packaged workbook  with the question, because (1) it ensures that data and formulas are are as you define them and (2) makes it easy for helpers to play their way to a solution.

             

            The second best solution would be to share a Excel file with the date periods and bank holidays.

             

            Ps. I find your formula interesting, because it is a variation of a formula I admire, namely  Mary Solbrig's  in  The specified item was not found..  The variation is that  line 1 has 'sunday'  and  line 3 has 5  as parameters in yours. I mention this so (1) it can benefit random readers and (2) as a note for my own benefit.

             

              DATEDIFF('week',[StartDate],[EndDate])*5
            - MIN(DatePart('weekday',[StartDate]),6)
            + MIN(DatePart('weekday',[EndDate]),6)
            
            
            
            
            
            
            
            
            
            
            
            
            
            • 3. Re: How to determine if a certain date falls within a date range
              Mark Riley

              Thank you for your responses so far, they gave me a couple of ideas.

               

              How about this

               

              I've created a parameter with my list of UK holidays (called Holidays) I then created the calculated field below

               

              if      [Parameters].[Holidays] <= today() AND [Parameters].[Holidays] >= [TARGET DUE DATE] THEN '1'

              END

               

              Am I right in thinking that this should look to see if one of my holiday dates (in the parameter list) falls between todays date and the target due date, if it does then it will assign a value of 1.

               

              I have then edited my original formula (the one in my first post) so that the working days between my two dates were calculated and then the result of the formula above is subtracted, hopefully leaving me with working days less any holidays that occur in that period. Does this make sense?

               

              DATEDIFF('week',today(),[ACTIVITIESMODDATE], 'sunday')*5

              + MIN(DATEPART('weekday',[ACTIVITIESMODDATE]),6)

              - MIN(DATEPART('weekday',today()),5)

              - int ([Holidays])  

               

              Mark

              • 4. Re: How to determine if a certain date falls within a date range
                kettan

                Here is a more advanced but hopefully relative simple solution that can help you.

                 

                1

                The first step was to create data. In my case, I created* an Excel file with two sheets, one with day numbers for period of interest, and one with Easter date to use as base for Easter related holidays.

                 

                2

                The second step was to connect to the file with custom SQL via the legacy connector.

                 

                SELECT dateadd('d', ['Day No$'].[Day No] - 1, <Parameters.Start Date>) AS [Calendar Date]
                , ['Easter Date$'].[Easter Year]
                , ['Easter Date$'].[Easter Date]
                FROM ['Day No$'], ['Easter Date$']
                WHERE YEAR(dateadd('d', ['Day No$'].[Day No] - 1, <Parameters.Start Date>)) = ['Easter Date$'].[Easter Year]
                AND dateadd('d', ['Day No$'].[Day No] - 1, <Parameters.Start Date>) <= <Parameters.Target Due Date>
                
                
                
                
                
                
                
                
                

                 

                3

                The third step was to create a formula for workday. It is a row level calculation and therefore easy to use in a view.

                 

                // Weekend
                IF     DATEPART('weekday', [Calendar Date]) = 7 THEN 0
                ELSEIF DATEPART('weekday', [Calendar Date]) = 1 THEN 0
                
                // Easter related holidays
                ELSEIF DATEDIFF('day', [Calendar Date], [Easter Date]) =  2 THEN 0
                ELSEIF DATEDIFF('day', [Calendar Date], [Easter Date]) = -1 THEN 0
                
                // Fixed holidays
                ELSEIF MONTH([Calendar Date]) =  1 AND DAY([Calendar Date]) =  1  THEN 0
                ELSEIF MONTH([Calendar Date]) = 12 AND DAY([Calendar Date]) = 25  THEN 0
                ELSEIF MONTH([Calendar Date]) = 12 AND DAY([Calendar Date]) = 26  THEN 0
                
                // workday
                ELSE 1
                END
                
                
                
                
                
                
                
                
                

                 

                Please notice that I have not included UK bank holidays. I hope you will do that part :-)  and share it in this thread.

                 

                I added a screenshot to help curious readers to decide if they want to know more without opening the attached workbook.

                thread 150545 How to determine if a certain date falls within a date range.png

                 

                * I didn't create it, but used (with minor changes) Johan Calendar #2.xlsm in  The Calendar Workbook Collection