Min / Max Time with multiple rows

Good morning all,

I am new here and pretty good with excel but having a roadblock on one of the Dashboards I am creating.

I have a list of people with multiple rows that have times in them. I need to be able to capture JUST the earliest time and the latest time for each person.


See below. Bill Smith has several rows for one day. He logged into the system at 9:00 (I need to capture that) And then logged out and back in a few times this day. His last log out was at 17:04. I need to capture that as well.



Capture.PNG

Hmm, when I click on attachments it does't open up any type of options to choose a file. Just a small thin box.

Capture.PNG

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

Hope this helps.

Pete

Thank you so much Pete!

Attached is the sample file.

Thanks modytrane.
I should have specified that there are multiple employees and dates on the spreadsheet So I imagine using some sort of sumifs, index, or something along those lines along with the min arguemnt for log in and then a separate formula with the max argument for log out
I would be looking up
If employee name (Bill Smith)
If this date (12/6/2018)
Give me the minimum of the log in times in column C

maybe using these 2 array formulae
=MIN(IF((B2:B49=K1)*(INT(C2:C49)=K2),C2:C49))
=MAX(IF((B2:B49=K1)*(INT(D2:D49)=K2),D2:D49))
Like

Thanks Fluff13!
I think that may do the trick. I will play with that and let you know if there are any issues.

You're welcome & thanks for the feedback.

Hi all,

So it looks like the above formula is not working. It is still pulling the overall earliest (minimum) time.
See column L an M for formula. It is pulling the overall earliest time in the entire sheet for Bill. Same for log out time.
For Tony is is giving generic 12:00am for both.

Those are array formulae & need to be confirmed with Ctrl Shift Enter not just Enter.
If done correctly excel will wrap the formula in {}

Those are array formulae, which means that you have to commit them using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual Enter.

Select cell L2 and click in the formula bar as if to edit the formula, then hold down the Shift and Ctrl keys and press Enter. You should see curly brackets { and } around the formula in the formula bar if you have done this correctly. Do the same with cell M2, and then you can copy those formulae down as far as you need to.

Hope this helps.

Pete

Excellent. Thanks Pete and Fluff.

I haven't used array formula's before. One question - Does the formula need "start" and "stop" parameters or can it be more generic?
Like instead of B2:B50 could it be B:B

It is not a good idea to use full-column references with an array formula, as every cell in the range will be examined (i.e. 1 million plus of them). You can, of course, use ranges which are larger than you expect to use, e.g. B2:B5000, C2:C5000 etc., but all ranges must be the same size. You need to use CSE again to commit the formula if you edit it.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Glad we could help & thanks for the feedback