» Finding the First Login and Last Logout Times of Employees

CATEGORY - Excel Date & Time Formulas VERSION - All Microsoft Excel Versions

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<>0)*(1/$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 and simultaneously press .