Looking Up Earliest and Latest Time Values

[warhead92100]

Hi Guys,

I need advise on how to make this spreadsheet work.

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.

[sathiyamoorthy]

can you please upload a sample sheet, your link is not opening from my office, it is blocking.

[warhead92100]

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

[Fotis1991]

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

[warhead92100]

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.

[Fotis1991]

A simple way could be this.

In M2 of sheet1 and copy down use this.

=C2-INT(C2)

If you like hide this column.

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

=MIN(IF(Sheet1!$A2:$A$10000=$A2,IF(Sheet1!$G$2:$G$10000=C$1,Sheet1!$M$2:$M$10000)))

Format these cells as Time.

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

[XOR LX]

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

[warhead92100]

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!

[XOR LX]

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

Regards

[kvsrinivasamurthy]

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

[warhead92100]

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.

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:

=IF(H2<>"2",WORKDAY(C2,365),IF(E2>TIMEVALUE("12:00:00"),INT(C2),WORKDAY(C2,-1)))

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,

[kvsrinivasamurthy]

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

[warhead92100]

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!

[warhead92100]

Anyone?

Thanks for any ideas and advice.