Excel problem - identifying the correct Login and Logout of an employee based on range

[Homiemeister]

Hi everyone,

I am having trouble with this one, please help me.

I have attached a sample file to better explain the situation. I have a data extracted for the employee's daily activity (Dump tab). The dump file shows 10 sample employees named Agent1 - 10 and their activities for the whole shift (Login, Break, Work, etc.). I need to get the EXACT/CORRECT Login and Logout of the employee for the shift based on the given timestamp per activity. The problem is, some of the employees' data shows start 0:00:00, end 23:59:59 for some reasons, but it wasn't their real login and logout. So everytime i try to MIN/MAX the range, it shows the min 0:00:00 and max 23:59:59.


Sample:
Agent1 scheduled 18:00:00 to 3:00:00
Agent1's whole shift activities goes:

LILO.JPG

SAMPLE.xlsx

Thank you very much in advance!

[zbor]

isn't this suppose to be a next day?

Foe xample, Agent3:

Please Login or Register  to view this content.

I assume he has been working since 30.08.2016 18:57:49 till 31.08.2016 3:58:33

[Homiemeister]

isn't this suppose to be a next day?

Foe xample, Agent3:

Please Login or Register  to view this content.

I assume he has been working since 30.08.2016 18:57:49 till 31.08.2016 3:58:33

Hi,

That was actually a good question. I tried converting the dates/time to same day and I guess it worked. Please see the attached file, if I'm looking at the right solution now. Thank you.SAMPLE.xlsx

[ELeGault]

Are you trying to just get the min and max of the two ranges?

12 AM is 0 in the world of numbers
11:59:59 is 24 hours or 1 for 1 complete day cycle but really is 0.999988425925926 so lets just say less than .999 to illustrate we mean just before midnight

Please Login or Register  to view this content.

Note - These are "Array" formulas so you will need to Hold CTRL and SHIFT and hit ENTER to apply them.

If you want to add to the criteria...meaning you want to have this by date or for a specific agent name you would need to NEST if statements where each new layer is another filter

For Example - if I wanted to see the minimum time for 8/30/2016. In my Example I have placed 8/30/2016 inside N1

Please Login or Register  to view this content.

[Homiemeister]

whoa, learned a lot today! thank you very much for your help, zbor and ELeGault. It's working now!

[ELeGault]

Always a pleasure - Be sure to share the knowledge as you obtain it

Please make sure you mark this as solved if you are satisfied with the solutions provided.

Cheers