5 Replies Latest reply: Mar 26, 2017 8:10 PM by Stefan Wühl RSS

    Fractional Workdays Between Two Time Stamps, Excluding Weekends

    Chris Luscombe

      Working on a application that include lots of time stamps for how long a work activity stays in a certain state.

       

      I need to figure out the number of fractional days elapsed between each time stamp taking the following into consideration, excluding weekend.

       

      If I just wanted to know work days between two dates, networkdays function would work great.  But I also need to consider the hours.

       

      For Example

      Time In:     3/17/2017   9:00:00 AM

      Time Out:   3/21/2017  12:01:00 PM

       

      In the above example this task took the 17 (Friday), 20(Monday), and then 4 hours on the 21(tuesday, so I would expect a result of 2 days 4 hours (or 2.5 days as there are 8 hours in a work day).

       

      I am stumped on this!

       

      Thanks

      Chris

        • Re: Fractional Workdays Between Two Time Stamps, Excluding Weekends
          Chris Luscombe

          Hi Stephan -

          Thanks for link.  Only part of I am confused about is how to get the output for duration in the right format.

           

          For example, in the second record we have

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

           

          I am getting a duration output of 5:13:00 AM.  Not sure what that is suppose to mean.

           

          I suspect this has something to do with formating dates, but I can seem to figure it out.  I am looking for duration formatting that would look something like 1.5 to represent the 1.5 work days between DT1 and DT2.

           

          I see you included TimestampFormat as a variable, but not sure if I should be using that to help with this.

           

          Thanks again.

           

          Chris

            • Re: Fractional Workdays Between Two Time Stamps, Excluding Weekends
              Stefan Wühl

              Hi Chris,

               

              the duration could be formatted using

               

              =Interval( DurationFieldOrExpression, 'h:mm:ss')

               

              to get rid of the AM / PM which is not correct for a duration.

               

              But you are interested in showing the duration in fractions of a working day. 8 hours are represented as 1/3 of a day, so you need to divide the duration by 1/3:

               

              =Num( DurationFieldOrExpression / (1/3), '0.0','.',',' )

               

              DurationFieldOrExpression in both example lines is the field or expression as shown in the post I've linked to.

                • Re: Fractional Workdays Between Two Time Stamps, Excluding Weekends
                  Chris Luscombe

                  So something like this?

                   

                  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

                  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,

                      num(interval(sum(End-Start)/1/3),'0.0','.',',' ) as Duration

                  Resident TMP

                  where WeekDay(Date)<5

                  and not match(Date,$(vHol))

                  group by ID;

                   

                  drop table TMP;

                   

                  Results for duration still seem wacky.  Attaching screen shot of straight table showing results.exmaple results.jpg

                    • Re: Fractional Workdays Between Two Time Stamps, Excluding Weekends
                      Stefan Wühl

                      Almost. A small typo in the duration calculation (and it seems you are using a 10h working day?)

                       

                       

                       

                      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

                      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),'h:mm:ss') as DurationHours,

                          num(sum(End-Start)/(10/24),'0.0','.',',' ) as Duration

                      Resident TMP

                      where WeekDay(Date)<5

                      and not match(Date,$(vHol))

                      group by ID;

                       

                      drop table TMP;

                       

                      ID DT1 DT2 Sum(Duration) Sum(DurationHours)
                      17,628333333321 176:17:00
                      28/18/12 09:20 AM8/20/12 01:13 PM0,521666666661625:13:00
                      38/17/12 09:20 AM8/20/12 01:13 PM1,388333333324413:53:00
                      48/17/12 09:20 AM8/19/12 01:13 PM0,866666666662798:40:00
                      56/27/12 12:41 PM7/6/12 4:38 PM7,395000000012873:57:00
                      66/29/12 4:45 PM7/6/12 4:19 PM4,956666666665149:34:00
                      78/1/12 09:00 AM8/3/12 10:00 AM2,099999999994221:00:00
                      88/3/12 03:00 PM8/6/12 09:00 AM0,44:00:00