1 Reply Latest reply on May 14, 2009 6:13 AM by ewid

    Working days between two dates using next row value




      I am trying to calculate the working days and hours between two dates from I can see how long an incident has been in a specific status.  I have all of the data I need to do the calculation but I am having problems with the formula.  The following formula works when I am calculating the different between two dates on the same row:


      Local DateVar Start := Date({pm_process.pm_creation_date});   //Starting Date here
      Local DateVar End := Date({pm_process_assignment.pm_creation_date});  //Ending Date here
      Local NumberVar Weeks;
      Local NumberVar Days;

      Weeks:= (Truncate (End - dayofWeek(End) + 1
      - (Start - dayofWeek(Start) + 1)) /7 ) * 5;
      Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
      (if DayOfWeek(Start) = 1 then -1 else 0)  +
      (if DayOfWeek(End) = 7 then -1 else 0); 

      The problem I have is that I now need to calculate the different between the date on the current row (Start Date) and the date on the next row (End date).  Does anybody know how to do this?


      I can achieve this using DateDiff (DateDiff ("d", {pm_process_assignment.pm_creation_date},next({pm_process_assignment.pm_creation_date}) )) which gives the total number but I only want working days.  I have tried adding the 'next' element but it conflicts with the 'WhileReadingRecords'.


      Any suggestions would be much appreciated.





        • 1. Re: Working days between two dates using next row value

          I have dummies solution   but works for me:


          local numbervar counter;

          local numbervar max_counter;

          local numbervar day_count;

          local datevar jump_date;


          day_count := end - start;

          max_counter := day_count - 1;

          jump_date := end;

          for counter:= 1 to max_counter do


                 jump_date := jump_date - 1;

                 if (dayofweek(jump_date)=1) or (dayofweek(jump_date)=7) then day_count := day_count - 1;


          "working days"&totext(day_count,0);