earlest date not complete

[Guest]

I got a number of cell the first has the date the second has a number of
entry's in it, being completed, ss, inv, tl, and mgr, what I need is a way to
get the oldest date that do not have completed, so is there any way to get
the answer for this, it is to produce some thing like,

a b c
4/1/07 completed oldest date not completed 30/1/07
5/2/07 completed
6/2/07 tl
3/3/07 ss
30/1/07 tl

any ideas
thanks

 

[Bernard Liengme]

Try =MIN(IF(B1:B5<>"completed",A1:A5,99999))
you must enter this array formula with Shift+Ctrl+Enter not just Enter
best wishes

 

[Bernard Liengme]

Forgot to add: You will need to format the cell as a date

 

[Guest]

Try

=min(if(B1:B10<>"completed",A1:A10))

Enter the array formula with CTRL SHIFT ENTER

The formula should display with {} around it when you are done.

 

[Bernard Liengme]

That gives zero, since cells with 'complete' put zero (false) in the output
array
best wishes

 

[Guest]

umnboth these I have tryed, I've now come up with using another cell with
=if(b1:b100="completed","closed",if(a1:a100="","","open"))
and then {=min(z1:z100="open",a1:a100))} I then set the column of z to
hidden, that solved the problem, it was a long way about but if it works, it
will have to do,

thanks to those that tryed to help with this problem.