6 Replies Latest reply on Jun 12, 2013 7:25 AM by Emily Mueller

    Calculate time between shifts

    Emily Mueller

      Hello, I am trying to calculate time between shifts, but I can't figure out how to do it.  I have a shift start (startdtm), and shift end (enddtm). I have a calculation for shift duration, but I want to know number of hours between shifts.  Can anyone help me out?

       

      I have attached a packaged workbook if you have any questions about what I am trying to accomplish. 

       

      Thanks in advance!

        • 1. Re: Calculate time between shifts
          Tracy Rodgers

          Hi Emily,

           

          If I understand right, you want to find the difference between the startdtm times. To do this a calculated field similar to the following could be created:

           

          if abs(datediff('minute', max([startdtm]), lookup(max([startdtm]), -1)))>=60 then (abs(datediff('minute', max([startdtm]), lookup(max([startdtm]), -1))))/60

          else abs(datediff('minute', max([startdtm]), lookup(max([startdtm]), -1)))

          end

           

          Hope this helps!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Calculate time between shifts
            Emily Mueller

            Thanks Tracy, but actually what I want to do is calculate the difference between the most recent startdtm, and the last enddtm.  So I want to know how many hours it was from when they last punched out, to the next time they punched in.  Does that make sense?

            • 3. Re: Calculate time between shifts
              Joe Mako

              What if you change the formula for "Time off"

              from

              DATEDIFF('hour',max([enddtm]),min([startdtm]))

              to

              DATEDIFF('hour',Lookup(max([enddtm]),-1),min([startdtm]))

              as in the attached?

               

              Be aware that this uses a table calculations, and requires awareness of your pill types, notice that I changed "Day" to a Measure pill (an ATTR() pill, an aggregation), and set "eventdate" as the compute using.

               

              Using table calculations can get complex fast, especially when working with date data types, there are many expectations and gotchas. You are welcome to contact me if you would like to discuss details.

              • 4. Re: Re: Calculate time between shifts
                Emily Mueller

                Joe, this is great!  Thank you!! Maybe you can help me with the next step of this.  Now that I can determine how long it has been since their last shift, I have set up a calculated field to say "Reset" if their time off was more than 34 hours.  Is it possible to start summing their hours worked since a reset, or since they had 34 or more hours off?  We employ truck drivers and we need to follow strict rules about how many hours they can work without a "Reset".  So I want to show on this calendar what their cumulative hours are since a reset and until the next reset.  Does that make sense?

                 

                I have attached a packaged workbook that shows how I am using the Reset.

                • 5. Re: Re: Re: Calculate time between shifts
                  Joe Mako

                  How about a formula like:

                  If [Time off]>34 then SUM([Shift Length]) else PREVIOUS_VALUE(0)+SUM([Shift Length]) end


                  (with the commute using set the same as the other table calcs)

                  • 6. Re: Re: Re: Calculate time between shifts
                    Emily Mueller

                    Joe, I can not thank you enough! Once again I am amazed at what you have come up with!

                     

                    Thanks again!

                     

                    Emily