Finding the First Login and Last Logout Times of Employees

Problem:

Range cells A2:C10 contains the IDs and login and logout times of various employees.
Each person can log in and out several times a day.
We want to find the first time each employee logged in and the last time they logged out.

Solution:

To find the first login time for each unique ID in A13:A16, use the MAX function as shown in the following Array formula:
{=1/MAX((A13=$A$2:$A$10)*($B$2:$B$10

To find the last logout time for each unique ID in D13:D16, use the MAX function as shown in the following Array formula:
{=MAX(($A$2:$A$10=D13)*($D$2:$D$10))}

To apply Array formula:
Select the cell, press

My name is Dy. I maybe posting my question to the wrong thread please forgive me. I just find your post relative to my concern.

I have a "Running” employee logs - meaning both login and logout time are both in one column but on the next column, it indicates whether it is "In" or "Out".

My requirement is to find the FIRST login (Column I) and LAST logout (Column J) considering both employees working night shift (ex.10PM-7AM) - need to capture first login during the night say 3/7/2017 and last logout in the morning of 3/8/2017 AND a regular day shift (ex.3AM-12PM) - same date (3/8/2017)

Please see attached sample logs

Thank you all for your help!

Hi MarvinP,

Thank you for your inputs. Really great idea! This works perfectly fine for employees working in day shift where only one date is involved.

For night shift employees, that's where I find trouble.

I have attached the file and highlighted parts of Emp5 - this employee works night shift. He usually login at around 11PM and normally logout 9AM in the morning. The pivot table only captures the first login of Emp5 after 12midnight which is wrong because the FIRST login actually happened at around 11PM

Thanks for your help

Dy.

Thank you Marvin,

That would work. The only problem is that the logs contain the entire employee records..there were employees working on a flexible schedule starting at night and ends in the morning (it could be 9PM-6AM, 10PM-7AM, 11PM-8AM, etc) - a mixed day and night shift in one file - this means I have no control of who is changing/adjusting schedule from time to time.

The goal is to capture the FIRST login and LAST logout regardless of the shift schedule.

Hoping there are other ways?

Cool, Thanks for your help!

Originally Posted by dy137

How about something like creating a new column for the night shifters? You would subtract 1 from the day, only for the night shift people. Then reverse the Min and Max?

Hi Sir, I will consider this...not sure if I get the idea here, can you please show me how to calculate?

Hello Admin,

Apologies if it violates the forum rule, I was researching for similar questions, taking chances to get a response and finally got lucky with MarvinP answers..

You are too good, i am also stuck in this option but didnt find this useful until i checked the cell format..
when i extracted this sheet from the attendance system it was in text and after seeing yours i got idea to multiply all times with 1 to convert it to Custom format.. :-) thanks @marvin

We use punch card option in my office. It tracks all Turnstile IN as well as Turnstile Out. So it is easy to find it out.