I need to determine how long between time punches that are on different rows

[taylorsm]

Data looks like this. I need to know how long Peter was out on lunch. So it would be like D2-F1 = 1 hour, then you'd have to skip because the 5:01 is clock out for the day, then the next several days too, then on row 9, its D10-F9 = w/e

I'm fine if there is an error or w/e because of the 5pm clock out. Whatever is the easiest.


Last Name First Name In date In Time Out Date Out Time Hours Out
Parker Peter 2/1/2019 7:52:00 AM 2/1/2019 1:37:00 PM
Parker Peter 2/1/2019 2:37:00 PM 2/1/2019 5:01:00 PM
Parker Peter 2/5/2019 7:50:00 AM 2/5/2019 5:32:00 PM
Parker Peter 2/6/2019 7:51:00 AM 2/6/2019 5:43:00 PM
Parker Peter 2/7/2019 7:56:00 AM 2/7/2019 5:40:00 PM
Parker Peter 2/8/2019 7:59:00 AM 2/8/2019 5:01:00 PM
Parker Peter 2/11/2019 8:00:00 AM 2/11/2019 1:12:00 PM
Parker Peter 2/11/2019 2:04:00 PM 2/11/2019 5:15:00 PM
Parker Peter 2/13/2019 7:42:00 AM 2/13/2019 5:40:00 PM
Parker Peter 2/14/2019 7:57:00 AM 2/14/2019 5:00:00 PM
Parker Peter 2/15/2019 7:50:00 AM 2/15/2019 5:00:00 PM
Parker Peter 2/18/2019 9:03:00 AM 2/18/2019 12:27:00 PM
Parker Peter 2/19/2019 7:55:00 AM 2/19/2019 5:30:00 PM
Parker Peter 2/21/2019 7:44:00 AM 2/21/2019 5:30:00 PM
Parker Peter 2/22/2019 7:57:00 AM 2/22/2019 5:05:00 PM
Parker Peter 2/25/2019 8:07:00 AM 2/25/2019 12:00:00 PM
Parker Peter 2/25/2019 1:00:00 PM 2/25/2019 5:02:00 PM

[XLent]

Putting in expected results helps but, if we assume (per your sample file) that your data is always sorted by Cols A, B, C & D (in that order) then perhaps:

Please Login or Register  to view this content.

the above would generate 1:00 in G3, 0:52 in G9 and 1:00 in G18 -- all other cells would return a null string (i.e. appear blank)

[taylorsm]

thanks! wish there was a format for it, had to do custom h:mm:ss and that appears to have worked