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!

 Date Name Time Room 11/19/2018 EMPLOYEE1 20:58:04 IN - DOOR 11/19/2018 EMPLOYEE1 21:40:56 OUT - DOOR 11/19/2018 EMPLOYEE1 21:46:10 IN - DOOR 11/19/2018 EMPLOYEE1 23:00:42 OUT - DOOR 11/19/2018 EMPLOYEE1 23:01:58 IN - BREAKROOM 11/19/2018 EMPLOYEE1 23:58:03 OUT - BREAKROOM 11/20/2018 EMPLOYEE1 0:00:04 IN - DOOR 11/20/2018 EMPLOYEE1 1:55:39 OUT - DOOR 11/20/2018 EMPLOYEE1 1:58:15 IN - BREAKROOM 11/20/2018 EMPLOYEE1 2:01:13 OUT - BREAKROOM 11/20/2018 EMPLOYEE1 2:03:01 IN - DOOR 11/20/2018 EMPLOYEE1 3:15:49 OUT - DOOR 11/20/2018 EMPLOYEE1 3:16:03 IN - BREAKROOM 11/20/2018 EMPLOYEE1 3:28:23 OUT - BREAKROOM 11/20/2018 EMPLOYEE1 3:29:50 IN - DOOR 11/20/2018 EMPLOYEE1 5:13:12 OUT - DOOR 11/20/2018 EMPLOYEE1 5:19:33 IN - DOOR 11/20/2018 EMPLOYEE1 6:02:44 OUT - DOOR

• ###### 1. Re: Difference between time

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