3 Replies Latest reply on Mar 29, 2017 2:29 PM by Kaz Shakir

    Nested DateDiff Function

    mozammel chowdhury

      Hello I have a nested datediff function in MS Access that I am trying to convert to tableau. I know how to convert a regular datediff however when I try the nested it does not work. Is there a workaround that somebody can suggest me. Following is a sample of what the nested Datdiff function looks in MSAcess


      DateDiff ("n",[Lunch Taken],

                                                 (DateDiff("n", [ARSiteTM] , [DTSiteTM] ) / 60)) /60) /24



        • 1. Re: Nested DateDiff Function
          Kaz Shakir


          What is that function supposed to be calculating?  The inner DateDiff, seems to be calculating the number of minutes between [ARSiteTM] and [DTSiteTM]; and then dividing that number by 60 which would produce the number of hours (or fractions of an hour) that have elapsed between those times.  Now, that result is going into the outer DateDiff as the third argument - but the third argument is supposed to be a date value, and this is just a number of hours.  And so, it doesn't really make any sense to me as to how you can find the difference between [Lunch Taken] (which I presume is a date-time value) and just a number of hours value.


          Are you trying to find the amount of time elapsed between [Lunch Taken] and the later of [ARSiteTM] and [DTSiteTM]?  If so, you could do a comparison of those fileds and just take the one that has the higher value; or vice-versa if you are trying to do the opposite.


          If you can provide a little more detail about your goal, we might be able to come up with some ideas for you.


          • 2. Re: Nested DateDiff Function
            mozammel chowdhury

            Thanks KAZ,

                                Essentially [Lunch Taken] ,[ ARSiteTM ]and [ DTSiteTM] are all datefields. So the goal is to get the actual time worked. So AR site is the start time of work, DT site is the end time of work. Then [Lunch Break] is formatted as an hour format instead of int showing how much should be excluded from the total work. So I am not sure what I can do here. Let me know if you need additional clarification.

            • 3. Re: Nested DateDiff Function
              Kaz Shakir

              In Tableau, can't you just do simple math, like this:


              [DTSiteTM] - [ARSiteTM]



              [AllHoursAtWork] - [Lunch Taken]




              Would something that simple work?