# Looking Up Earliest and Latest Time Values

1. ## Looking Up Earliest and Latest Time Values

Hi Guys,

http://www.mediafire.com/?l5c9wb73cns9rt0

Sorry for the link, the file is tagged to be to large. Anyway, I need to search for the earliest time in Sheet1!E:E, this would need to be filtered by dates in Sheet1!G:G.

Does anybody have ideas on how to make this work?

Thanks in advance for any help.

3. ## Re: Looking Up Earliest and Latest Time Values

How do I attach my file again? There's no option for it now.

4. ## Re: Looking Up Earliest and Latest Time Values

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

5. ## Re: Looking Up Earliest and Latest Time Values

Thanks!
Btw, example is in Sheet3!C2, it needs to show the earliest time from Sheet1!, with Filters of Employee 1 and Oct 1.

Again thank you for any help or idea you can give.

6. ## Re: Looking Up Earliest and Latest Time Values

A simple way could be this.

In M2 of sheet1 and copy down use this.
Formula:
`Please Login or Register  to view this content.`

If you like hide this column.

Then in C2 of your other sheet and copy down and across use this ARRAY formula.

Formula:
`Please Login or Register  to view this content.`

Format these cells as Time.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

7. ## Re: Looking Up Earliest and Latest Time Values

Or, perhaps this array formula in C2 and copy across and down:

=TEXT(MIN(IFERROR(1/(1/(IF(Sheet1!\$K\$2:\$K\$5226=CONCATENATE(0+Sheet3!C\$1,Sheet3!\$A2),MOD(Sheet1!\$C\$2:\$C\$5226,1)))),"")),"h:mm AM/PM")

Regards

8. ## Re: Looking Up Earliest and Latest Time Values

Thanks! Rep added! however, will there be a way for me to set that if time is PM, use MIN, if time is AM use MAX? or another way around is the check column in Sheet1, as only valid entry wound be from 2F and Time in is in PM (Time out is AM). Sorry for making this too troublesome.

Thanks again in advance for any help!

9. ## Re: Looking Up Earliest and Latest Time Values

Sorry - whose solution are you referring to? Mine or Fotis's?

Regards

10. ## Re: Looking Up Earliest and Latest Time Values

Pl see attached file.
Formulas in E & G columns in Sheet1 is changed.I feel this is what is required.

11. ## Re: Looking Up Earliest and Latest Time Values

Originally Posted by XOR LX
Sorry - whose solution are you referring to? Mine or Fotis's?

Regards
Any of the two, or whichever can be flexible enough to have many criteria.

Originally Posted by kvsrinivasamurthy
Pl see attached file.
Formulas in E & G columns in Sheet1 is changed.I feel this is what is required.
Thanks! However, I tried to update the formula in G column to:

``Please Login or Register  to view this content.``
As I need to filter the entries to only those that are from door 2F-Prod Door. But it doesn't affect the values in Sheet3. Is there any way to tweak the formula to add the door filter?

Thanks a lot guys,

12. ## Re: Looking Up Earliest and Latest Time Values

Filter for Entry from door 2F-Prod Door or Exit from door 2F-Prod Door or for both.

13. ## Re: Looking Up Earliest and Latest Time Values

Is the formula not sensitive to blank cells? the formula above kinda filters the door, making non 2/f entry blanks. As the source actually being generated on a real time basis I would need to have a check point to filter the 2/f data without deleting the other entries. sorry for this troublesome request.

TIA!

14. ## Re: Looking Up Earliest and Latest Time Values

Anyone?

Thanks for any ideas and advice.

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