11 Replies Latest reply: Sep 9, 2014 8:10 AM by ritesh agarwal RSS

    Network Days

      Hi,

       

      I have an Opened Date and a Closed Date. I need the exact difference between the two excluding Weekends and Holidays in num format.

      Using N/w days gives me value in whole numbers.

       

      Eg:

       

      Open DateClosed dateDiff
      8/9/12 11:08 AM8/9/12 2:57 PM0.16

       

      Regards.

        • Re: Network Days
          Stefan Wühl

          You can try something like this:

           

          Set TimestampFormat = 'M/D/YY hh:mm TT';

           

          LOAD *,

          num(interval(

          NetWorkDays(DT1+1,DT2-1)

          +(

          if(NetWorkDays(DT2,DT2),frac(DT2)+(floor(DT1)<>floor(DT2))*-1,1)

          -if(NetWorkDays(DT1,DT1),frac(DT1),1)

          )

          )) as Diff;

          LOAD * INLINE [

          DT1, DT2

          8/9/12 11:08 AM,8/9/12 2:57 PM

          8/18/12 09:20 AM,8/20/12 01:13 PM

          8/17/12 09:20 AM,8/20/12 01:13 PM

          8/17/12 09:20 AM,8/19/12 01:13 PM

          ];

           

          You'll need to add your list of holidays to each single Networkdays() function as third argument (check the Help for more details).

           

          Regards,

          Stefan

            • Re: Network Days

              Thanks Stefan.. But the values are one day lesser... The correct value has to be 2.94, but gives 1.94...

               

              Secondly,

               

              if(NetWorkDays(DT2,DT2),frac(DT2)+(floor(DT1)<>floor(DT2))*-1,1)

              -if(NetWorkDays(DT1,DT1),frac(DT1),1)

              )

               

              should i be adding the holiday list like below in the other 2 n/w days after defining  a variable fr holidays.

               

              if(NetWorkDays(DT2,DT2),frac(DT2)+(floor(DT1)<>floor(DT2))*-1,1,$(vhol))

              -if(NetWorkDays(DT1,DT1),frac(DT1),1,$(vhol))

              )

                • Re: Network Days
                  Stefan Wühl

                  Thanks Stefan.. But the values are one day lesser... The correct value has to be 2.94, but gives 1.94...

                  The correct value for which pair of timestamp values?

                   

                   

                  Secondly,

                   

                  if(NetWorkDays(DT2,DT2),frac(DT2)+(floor(DT1)<>floor(DT2))*-1,1)

                  -if(NetWorkDays(DT1,DT1),frac(DT1),1)

                  )

                   

                  should i be adding the holiday list like below in the other 2 n/w days after defining  a variable fr holidays.

                   

                  if(NetWorkDays(DT2,DT2),frac(DT2)+(floor(DT1)<>floor(DT2))*-1,1,$(vhol))

                  -if(NetWorkDays(DT1,DT1),frac(DT1),1,$(vhol))

                  )

                   

                  You need to add the variable as argument to the NetworkDays function, not to the if() statement.

                    • Re: Network Days

                      Eg:

                       

                      idopen timeclosed timeDiff from qvwactual diff to be
                      12346/27/12 12:41 PM7/6/12 4:38 PM6.1646759267.164675926
                      234456/29/12 4:45 PM7/6/12 4:19 PM3.9818634264.981863426
                        • Re: Network Days
                          Stefan Wühl

                          I do get 7.16 and 4.98 with the code I posted above. Could you double check your script? Or post the code snippet you're using?

                            • Re: Network Days

                              Thank you.. It works fine..

                               

                              Using the same n/w days, i also need to calculate the difference b/w 2 dates excluding sundays for RegionA and excluding Mondays for RegionB and of course national holidays as well as holidays based on regions. any ideas?

                              Regards.

                                • Re: Network Days

                                  Any ideas on atleast difference between two days exclusing only Sundays and Holidays??

                                    • Re: Network Days
                                      Stefan Wühl

                                      Networkdays function will always exclude saturday and sundays.

                                       

                                      If your holidays are never on a saturday, you can maybe modify above code to check the weekday for the begin and end date and add the number of saturdays inbetween. But I believe you could have saturdays as holiday, right?

                                       

                                      You can also create a calendar with all sundays and holidays flagged. Then... Hm, probably needs a rather complex script to check the calendar for each date in your interval.

                                       

                                      Or browse the internet and search for a VBScript or JavaScript function that might help you. I am pretty sure you should be able to find something.

                                       

                                      For example, this looks quite promising (for a start):

                                      http://forums.devshed.com/visual-basic-programming-52/calculate-time-difference-between-two-dates-216869.html

                                       

                                      You can create a QV macro code from this and call the functions in the script, just like QV functions.

                                       

                                      Just some ideas.

                                        • Re: Network Days
                                          Stefan Wühl

                                          Another idea:

                                           

                                          Set TimestampFormat = 'M/D/YY hh:mm TT';

                                           

                                          Set vHol = '41130,41140';

                                           

                                          INPUT:

                                          LOAD *, recno() as ID INLINE [

                                          DT1, DT2

                                          8/9/12 11:08 AM,8/9/12 2:57 PM

                                          8/18/12 09:20 AM,8/20/12 01:13 PM

                                          8/17/12 09:20 AM,8/20/12 01:13 PM

                                          8/17/12 09:20 AM,8/19/12 01:13 PM

                                          6/27/12 12:41 PM,    7/6/12 4:38 PM

                                          6/29/12 4:45 PM,    7/6/12 4:19 PM   

                                          ];

                                           

                                          TMP:

                                          LOAD  ID,

                                          daystart(DT1)+iterno()-1 as Date,

                                          if(iterno()=1, frac(DT1), 0) as Start,

                                          if(daystart(DT1)+iterno()-1=daystart(DT2), frac(DT2),1) as End

                                          Resident INPUT

                                          while daystart(DT2) >= daystart(DT1)+iterno()-1;

                                           

                                          left join (INPUT) LOAD

                                          ID,

                                          sum(End-Start) as Duration

                                          Resident TMP where WeekDay(Date)<6 and not match(Date,$(vHol)) group by ID;

                                           

                                          drop table TMP;

                                           

                                          in above where clause, you can set your requirements. If you want to exclude sat and sun, use where Weekday(Date)<5.  I put in some few fake holidays in variable $(vHol), but you can build the variable also from a holiday table, there are some samples here in the forum on how to do this.

                          • Re: Network Days

                            Hi Stefan,

                             

                            What is we have 10 hrs as working for weekday and 4hrs working for sat. we have to exclude sunday and holidays.

                            How can we modify the below code:

                             

                             

                            INPUT:

                            LOAD *, recno() as ID INLINE [

                            DT1, DT2

                            8/9/12 11:08 AM,8/9/12 2:57 PM

                            8/18/12 09:20 AM,8/20/12 01:13 PM

                            8/17/12 09:20 AM,8/20/12 01:13 PM

                            8/17/12 09:20 AM,8/19/12 01:13 PM

                            6/27/12 12:41 PM,7/6/12 4:38 PM

                            6/29/12 4:45 PM, 7/6/12 4:19 PM  

                            8/1/12 09:00 AM, 8/3/12 10:00 AM

                            8/3/12 03:00 PM, 8/6/12 09:00 AM

                            ];

                             

                            TMP:

                            LOAD  ID,

                            daystart(DT1)+iterno()-1 as Date,

                            if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

                            if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

                            Resident INPUT

                            while daystart(DT2) >= daystart(DT1)+iterno()-1;

                             

                            left join (INPUT)

                            LOAD

                            ID,

                            interval(sum(End-Start)) as Duration

                            Resident TMP  where WeekDay(Date)<5 and not match(Date,$(vHol))   group by ID;