8 Replies Latest reply: Dec 1, 2015 1:33 PM by Stefan Wühl RSS

    Networks days function

    Charles Mogashoa

      Good day

       

      Please assist with a network days function that will exclude weekends
      and round off the days to 2 decimal places.

       

      Eg. 6 hours = 0.25 days  or

      36 hours = 1.50 days or

      1 day 18 hours = 1.75 days

       

      This is for Turn Around Time (TAT) calculations.

       

      Regards

      Charles

        • Re: Networks days function
          Stefan Wühl

          Have a look for example at this suggested approaches:

          Re: Calculate hours between two Date/Time strings

           

          Adapt the start and end times as necessary (potentially even simplifying the expression when your start and end timestamps start and end with each day.

           

          To round to 2 digits, just add a Round() function

           

          Round(

          rangesum(

          NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)  

          // 10 hours per workday, for all day inbetween the period, excluding bounderies

          ,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

          ,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working hours first day

          ,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10)) // correct for first equals last day

          )

          , .01)

            • Re: Networks days function
              Charles Mogashoa

              Hi

               

              Can you please assist with a Round(Networkdays()) function to 2 decimal places where date includes time.

               

              My script is  Round(NetWorkDays(PF_DateStepStarted,Today(),$(vPublicHolidays)),2)

              but it's not working.

              PF_DateStepStarted format: 2015-11-30 17:26:30.000

               

              Thanks

               

               

                • Re: Networks days function
                  balraj ahlawat

                  try this:

                   

                  Round(NetWorkDays(PF_DateStepStarted,Today(),$(vPublicHolidays)),0.01)


                  Hope this will help

                    • Re: Networks days function
                      Charles Mogashoa

                      Hi

                       

                      It worked but it didn't convert the time to decimal.

                       

                      i'm getting 2.00 , 1.00, 5.00

                       

                      I think it needs a function to convert days to hours then say that eg. 30 hours is 1.25 days.

                        • Re: Networks days function
                          balraj ahlawat

                          NetworkDays() will return number of days, now if you want to convert it into Time like into Hours etc.

                           

                          try like:

                           

                          NetWorkDays(date(PF_DateStepStarted,'DD/MMM/YYYY'),date(Today(),'DD/MMM/YYYY/),$(vPublicHolidays))*24             //will convert it into Hours and then use Round().

                          • Re: Networks days function
                            Stefan Wühl

                            NetworkDays() function will only return complete days, i.e. integers. So you won't be able to do a more granular calculation only using this function. You'll need to consider the time of your timestamps by your own, as I showed above.

                             

                            Here is an adaption to 24 hour working days:

                             

                            SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
                            Let vHol = Num(Makedate(2015,12,25)); // just an example
                            
                            LOAD
                            Round(
                            rangesum(
                            NetWorkDays(DT1+1,DT2-1,$(vHol)) 
                            // 24 hours per workday, for all day inbetween the period, excluding bounderies
                            ,if(NetWorkDays(DT2,DT2,$(vHol)), frac(DT2) ,0) // working hours last day
                            ,if(NetWorkDays(DT1,DT1,$(vHol)),1 - frac(DT1) ,0) // working hours first day
                            ,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2), -1) // correct for first equals last day
                            )
                            , .01) as Interval,
                            *
                            INLINE [
                            DT1, DT2
                            28/11/2015 4:00:00 PM, 01/12/2015 4:00:00 AM
                            27/11/2015 4:00:00 PM, 01/12/2015 4:00:00 AM
                            ];
                            
                          • Re: Networks days function
                            balraj ahlawat

                            also make sure format of all date fields should be common like:

                             

                            let vPublicHolidays=Date(max(HolidayDate), 'DD/MMM/YYYY')      //Logic part can differ for same


                            Round(NetWorkDays(date(PF_DateStepStarted,'DD/MMM/YYYY'),date(Today(),'DD/MMM/YYYY/),$(vPublicHolidays)),0.01)


                            Hope it will be helpful...