# Off Topic > Tips and Tutorials >  >  Finding the First Login and Last Logout Times of Employees

## ExcelTip

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

----------


## dy137

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!

----------


## MarvinP

Hey exceltip and dy,

I think you can do this problem using a Pivot Table and showing a column of Min and another of Max.  If you group by date the answers appear.  No formulas needed.  See the attached.

First Time In and Last Time Out.xlsb

----------


## dy137

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.

----------


## MarvinP

Hey,

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?  You simply (might be hard) have to get the in and out to show the same day!

----------


## dy137

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?

----------


## MarvinP

You need to somehow tag those night shift check ins and outs.  Without doing that you don't have enough information to do the problem.  You might already know this.

----------


## dy137

Cool, Thanks for your help!

----------


## 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?

----------


## FDibbins

Perhaps you guys missed that you are posting on a thread that is almost 12 years old?

----------


## dy137

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..

----------


## FDibbins

I let it go because the OP actually had no responses to their question  :Smilie:

----------


## MarvinP

I certainly missed that small point!  Thanks for pointing that out.  I was worried we hijacked the OP's thread, but now I see I shouldn't worry too much.

----------


## FDibbins

We all miss stuff now and then, no problem, Marvin  :Smilie:

----------


## sbhadki

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

----------


## FDibbins

sbhadki welcome to the forum and thanks for the input  :Smilie:

----------


## MarvinP

Hi sbhadki and welcome to the forum,

When you said you multiplied by 1, you could also have added zero to get those text to numbers.

I don't know if you did it using Paste Special using Add like this site shows.
http://www.techrepublic.com/blog/mic...ting-in-excel/

----------


## ngthanhluan

New topic has been create on the link : https://www.excelforum.com/excel-for...ml#post4836151

Thanks protonLeah !

----------


## protonLeah

ngthanhluan,

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## Florence27

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.

----------

