oldest date not completed

[Dreamstar_1961]

Hi
what I'm after is a formula to return the oldest date not completed
currently I'm using the min command but that returns the oldest

 

[Bernie Deitrick]

Dreamster,

How is "Completed" indicated?

Perhaps, array enter (enter using Ctrl-Shift-Enter) something like

=MAX(IF(B1:B100="Completed",A1:A100,0))

HTH,
Bernie
MS Excel MVP

 

[Teethless mama]

=MIN(IF(B1:B100="Completed",A1:A100,0))

 

[Dreamstar_1961]

the formula only returned 00/01/00, what I'm after is to display the next
lowest date, not completed

A2 2/3/06 B2 completed
A3 3/3/06 B3 completed
A4 4/3/06 B4 running
A5 5/3/06 B5 ruuning
A6 6/3/06 B6 completed

it is to return 4/3/06 as that is not completed I tryed using the formula
with the small formula but don't know how to advance it to the next number

 

[T. Valko]

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MIN(IF(B2:B6<>"completed",A2:A6))

Format as DATE

Biff

 

[Dreamstar_1961]

still have not got this to work, when I enter it by an array it returns the
oldest date, the cell has also got empty cells, don't if that affects the
end result, I found this on the web and it works but dosn't help me
=MIN(IF(B2:B200=0,A2:A200)) entered as an array, but that was when they were
both dates any more ideas any one can think any other idea's on how to get
this to work

 

[Dreamstar_1961]

found the answer, was easyer than i thought, was as simple as using
=SMALL(A1:A100,COUNTIF(B1:B100,"Completed")+1)
thanks to those that helped me with this problem

 

[Dreamstar_1961]

sorry still not working, that is counting all the completed but is not trying
it to the cell next to it