1 Reply Latest reply on Nov 19, 2018 12:20 PM by Joe Oppelt

    Difference between time


      For example, employees worked from 09:00 pm to 06:00 am.

      I want to calculate the following:

      1. total minutes IN-DOOR per employee per shift.

      2. total minutes OUT-DOOR per employee per shift.

      3. total minutes IN-BREAKROOM per employee per shift.


      Employees do not have the same schedule. Some are morning shift, midshift and night shift.

      Thank you for helping!



      11/19/2018EMPLOYEE120:58:04IN - DOOR
      11/19/2018EMPLOYEE121:40:56OUT - DOOR
      11/19/2018EMPLOYEE121:46:10IN - DOOR
      11/19/2018EMPLOYEE123:00:42OUT - DOOR
      11/19/2018EMPLOYEE123:01:58IN - BREAKROOM
      11/19/2018EMPLOYEE123:58:03OUT - BREAKROOM
      11/20/2018EMPLOYEE10:00:04IN - DOOR
      11/20/2018EMPLOYEE11:55:39OUT - DOOR
      11/20/2018EMPLOYEE11:58:15IN - BREAKROOM
      11/20/2018EMPLOYEE12:01:13OUT - BREAKROOM
      11/20/2018EMPLOYEE12:03:01IN - DOOR
      11/20/2018EMPLOYEE13:15:49OUT - DOOR
      11/20/2018EMPLOYEE13:16:03IN - BREAKROOM
      11/20/2018EMPLOYEE13:28:23OUT - BREAKROOM
      11/20/2018EMPLOYEE13:29:50IN - DOOR
      11/20/2018EMPLOYEE15:13:12OUT - DOOR
      11/20/2018EMPLOYEE15:19:33IN - DOOR
      11/20/2018EMPLOYEE16:02:44OUT - DOOR


      Joe Oppelt please help! thank you.

        • 1. Re: Difference between time
          Joe Oppelt

          You can do:


          DATEDIFF('minute',[start date], [end date])


          to get the difference between two dates.

          But that means you want to shape the data so that the time is part of the date field.


          In your case you'd have to do a LOOKUP to get the next row:


          DATEDIFF('minute', ATTR([Date-time]), LOOKUP(ATTR([date-time]), 1) )


          The "1" in the LOOKUP says to look forward one row.


          Now you'll have values in minutes for each row (except the last row, which has no "next row" to look at.)


          I can see some pitfalls in this.  How will you know rows for Day-1 for employee-1 from Day-2 rows?  You'll need to put some sort of indicator in the data to trigger that for you.

          1 of 1 people found this helpful