3 Replies Latest reply: Jan 23, 2015 5:09 AM by Sanjyot Paktar RSS

    Calculation of networkdays

    Sanjyot Paktar


      @

      Hi

       

      Can anybody please help urgently?

       

      Need a simple solution

       

      I want to calcuate the elapsed days i.e. networkdays between date raised and resolved date.

       

      Simply using networkdays() doesnt give the right answer since some of the rows in raised and resolved date columns are right, center or even left aligned.

      have tried the below query but doesnt seem to work for all the rows.

       

       

       

      if(date([Resolved Date],'M/D/YYYY') > '', NetWorkDays(date([Date Raised],'M/D/YYYY'),date([Resolved Date],'M/D/YYYY')),NetWorkDays(date([Date Raised],'M/D/YYYY'), date(today(),'M/D/YYYY') )) as Elapsed_Days,

        • Re: Calculation of networkdays
          Gysbert Wassenaar
          since some of the rows in raised and resolved date columns are right, center or even left aligned.

          That means some of your dates are not dates, but text strings. You have to convert these in the script in real dates. You can use the alt function to capture the dates than aren't numeric dates already and convert them into dates using the date# function:

           

          LOAD

              date(alt(MyDate,date#(MyDate,'M/D/YYYY'),'M/D/YYYY') as MyDate,

              ...other fields....

          FROM ...somewhere...


          See this blog post for more information:  Get the Dates Right