# How to Get Earliest Time In and Latest Time Out

1. ## How to Get Earliest Time In and Latest Time Out

Hi experts!

I am trying to solve this for quite some time. I have a workbook with two sheets. On the first sheet it is the raw data. The raw data contains the actual log-ins and log-outs of the agents. On the second sheet is the report. My problem is, there are agents that have multiple log-ins and log-outs during the day. What I wat do is that on my LILO sheet I would only have the earliest log-in time and the latest log-out time of each agent.

Hope you can help thank you!

2. ## Re: How to Get Earliest Time In and Latest Time Out

Try using these array formulas entered with CTRL + SHIFT + ENTER

On the LILO sheet,
B3: =MIN(IF(Raw!\$A\$2:\$A\$61=A3,Raw!\$C\$2:\$C\$61))
C3: =MAX(IF(Raw!\$A\$2:\$A\$61=A3,Raw!\$E\$2:\$E\$61))

3. ## Re: How to Get Earliest Time In and Latest Time Out

B3: =MIN(IF(Raw!\$D\$2:\$D\$61=\$B\$1,IF(Raw!\$A\$2:\$A\$61=A3,Raw!\$C\$2:\$C\$61)))
C3: =MAX(IF(Raw!\$D\$2:\$D\$61=\$B\$1,IF(Raw!\$A\$2:\$A\$61=A3,Raw!\$E\$2:\$E\$61)))

Enter by pressing Ctrl+Shift+Enter

And if you want it to be blank if they didn't work that day, add ,"" before the last parentheses.

4. ## Re: How to Get Earliest Time In and Latest Time Out

Try these array formulas**:

Entered in B3:

=IFERROR(1/(1/MIN(IF(Raw!A\$2:A\$61=A3,Raw!C\$2:C\$61))),"")

Format as h:mm

Entered in C3:

=IFERROR(1/(1/MAX(IF(Raw!A\$2:A\$61=A3,Raw!E\$2:E\$61))),"")

Format as h:mm

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select B3:C3 and copy down as needed.

5. ## Re: How to Get Earliest Time In and Latest Time Out

Hi!

I am still having problems.

Consider this:

Dominic's shift is 22:00-07:00.
He logs in at 22:00 but accidentally logged out at 23:00, he immediately logs back in and ended his shift at 07:00. When i use the formula, the formula for the log-out reports 23:00 as his log-out and not 07:00.

Another one:
Rey's shift is 23:00-08:00
Here how is the Raw Report Looks like:

Name ----------------- Date ------------- Log-In -------------LogOut Date --------------- Log-Out
Rey 9/8/2013 23:00 9/9/2013 01:30
Rey 9/9/2013 01:30 9/9/2013 08:00
Rey 9/9/2013 23:00 9/10/2013 08:00

With the above data when I ran the MIN formula for Sept 9, it will return the value 01:30 and not 23:00.

Thanks!

6. ## Re: How to Get Earliest Time In and Latest Time Out

Now we have a real problem..

Is it fair to say that the data is sorted?
The Login time that is Closest to the TOP of the list for each person is the time you're looking for
The Logout time that is Furthest to the bottom of the list for each person is the time you're looking for

7. ## Re: How to Get Earliest Time In and Latest Time Out

That is absolutely correct... that is how the program generates its report.

8. ## Re: How to Get Earliest Time In and Latest Time Out

So for a person that works past midnight, which Date is considered the Date that person worked?
The date of Pre or Post Midnight?

9. ## Re: How to Get Earliest Time In and Latest Time Out

It will be pre-midnight

10. ## Re: How to Get Earliest Time In and Latest Time Out

This is a rather weird way to do it but here's what I've got:

=MIN(IF(Raw!\$B\$2:\$B\$61=\$B\$1,IF(Raw!\$A\$2:\$A\$61=A3,IF(Raw!\$D\$2:\$D\$61>\$B\$1,Raw!\$E\$2:\$E\$61,MAX(IF(Raw!\$A\$2:\$A\$61=A3,Raw!\$E\$2:\$E\$61))))))

11. ## Re: How to Get Earliest Time In and Latest Time Out

tried it and it works well with the log-out....

will this work as well for the log-ins?

Thanks!

12. ## Re: How to Get Earliest Time In and Latest Time Out

I'm sorry, I thought the previous formulas worked for the Login and you only needed a revision to the Logout.

=MIN(IF(Raw!\$B\$2:\$B\$61=\$B\$1,IF(Raw!\$A\$2:\$A\$61=A3,Raw!\$C\$2:\$C\$61)))

Does this work?

13. ## Re: How to Get Earliest Time In and Latest Time Out

Hi!

First, my warmest thanks for your help!

The formula works as it was designed... but I am still having problems to those having multiple log-ins and log-outs and their shift passes midnight.

Like in one case an agent's shift is from 8PM to 5AM. He logs-in on Tuesday at 8PM but accidentally logged-out at 1AM which is a Wedenesday and logs back in immediately. He finishes his shift and logs-out at 5AM. The formula will report that for the Tuesday shift he logs in at 8PM which is correct but will report his logout at 1AM instead of 5AM. The next coloumn for Wednesday will report his log-in as 1AM which is not correct.

I tried to do any revision possible but to no avail

Again my biggest thanks!

14. ## Re: How to Get Earliest Time In and Latest Time Out

Bump...
Sorry, I've been trying to think of a way to do this with a formula. My idea is to check to see if the last log in/log out time was within a certain variance (like 5 minutes) and if so, to disregard that entry, but I've been unable to wrap my head around it.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1