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

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


"Dreamstar_1961" wrote in message
...
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

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


"Dreamstar_1961" wrote:

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

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


"Teethless mama" wrote:

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


"Dreamstar_1961" wrote:

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

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" wrote in message
...
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


"Teethless mama" wrote:

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


"Dreamstar_1961" wrote:

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

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

"T. Valko" wrote:

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" wrote in message
...
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


"Teethless mama" wrote:

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


"Dreamstar_1961" wrote:

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

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" wrote:

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

"T. Valko" wrote:

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" wrote in message
...
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


"Teethless mama" wrote:

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


"Dreamstar_1961" wrote:

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

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

"Dreamstar_1961" wrote:

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" wrote:

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

"T. Valko" wrote:

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" wrote in message
...
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


"Teethless mama" wrote:

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


"Dreamstar_1961" wrote:

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