CODEWITHSUNDEEP
excelformula
Ace Ebreo
Ace Ebreo

Reputation: 11

Excel formula to measure duration of breaks

I am working on a formula which will measure the duration of time my employees went on break.

The data I have:

NAME    LOGIN   LOGOUT  TYPE    DATE
JOHN    5:00 PM 6:00 PM BREAK   8/1/2018
MARK    6:15 PM 7:00 PM BREAK   8/1/2018
LUKE    5:05 PM 5:00 PM BREAK   8/1/2018
MARK    6:20 PM 9:00 PM LUNCH   8/1/2018
LUKE    6:20 PM 9:20 PM LUNCH   8/1/2018
JOHN    6:15 PM 9:00 PM LUNCH   8/1/2018

I want to measure the duration of their breaks. For instance, John logged out at 6:00 PM for his break and then logged in again at 6:15 PM. Unfortunately, the data I have are not in chronological order hence I need to lookup the specific values first depending on the date. Could you please provide me with a formula that will lookup John's logout time tagged "break" on 08/1/2018 and then subtract it from his login time on the same date to get the duration.

Upvotes: 1

Views: 135

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use AGGREGATE to return the next login after the logout and subtract the current logout from that:

=IFERROR(AGGREGATE(15,6,($E$2:$E$7+$B$2:$B$7)/(($E$2:$E$7+$B$2:$B$7>E2+C2)*($A$2:$A$7=A2)),1)-(E2+C2),"No clock in")

enter image description here

Upvotes: 2

Related Questions