Oldest Date

[Amy]

I have 2 columns with dates, "Receipt" and "Completed". I'm trying to determine the oldest receipt date for the ones not completed. Any ideas?

Thanks. Amy

 

[Andy B]

Hi

Try this:
=MIN(IF(B2:B1000=0,C2:C1000))

This must be array-entered - which means typing the formula and pressing
Ctrl Shift and Enter - instead of just Enter. If you do it right, Excel will
surround the formula with curly brackets {}

--
Andy.

I have 2 columns with dates, "Receipt" and "Completed". I'm trying to

determine the oldest receipt date for the ones not completed. Any ideas?

 

[Peo Sjoblom]

=MIN(IF(Completed="",Receipts))

entered with ctrl + shift & enter

assuming not completed is a blank cells, otherwise replace "" with whatever
shows
a non completed receipt

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom

I have 2 columns with dates, "Receipt" and "Completed". I'm trying to

determine the oldest receipt date for the ones not completed. Any ideas?

 

[Andy B]

Hi

I've recreated it from scratch and it works for me! I think, however, that
the arguments I posted are the wrong way round! Try:
=MIN(IF(C2:C1000=0,B2:B1000))

--
Andy.

Thanks Andy B - Here's my example:

Rec Comp
6/1/04 6/2/04
6/1/04 6/2/04
6/2/04
6/2/04 6/3/04
6/3/04 6/3/04
6/3/04

With the formula mentioned below I get 1/0/00 as the result. If I don't

enter as an array I get 6/1/04 as the result. The answer I'm looking for is
6/2/04 because it's the oldest date that has not been completed. Any other
suggestions?

 

[Andy B]

Try:
=MIN(IF((B2:B1000>0)*(C2:C1000=""),B2:B1000))

--
Andy.

Thanks for all your work on this Andy B.

OK, I figured out the problem but still with no solution. If I limit my

ranges to the cells that have values I get the correct answer. How can I
expand the range without it pulling and reading the blank cells?