Need to find oldest date in ever changing list.

  • Thread starter Thread starter Alan Anderson via OfficeKB.com
  • Start date Start date
A

Alan Anderson via OfficeKB.com

My worksheet has Date.Ordered in COL-A and Date.Filled in COL-F. I can
find the oldest date in Date.Ordered by using the SMALL function. However,
when I fill that order, I need to know the next oldest date to be filled.
I cannot figure out how to make the formula look at only unfilled orders
(i.e where a date does not exist in Date.Filled).

Does anyone have a solution?
 
Assuming the data occupies rows 2:100, try this array formula (entered with
CTRL+SHIFT+ENTER):

=SMALL(IF(F2:F100="",100000,A2:A100),2)

100000 corresponds to a date in the year 2173, so empty dates in column F are
treated as being filled in 2173.
 
Hi!

Try this formula entered as an array with the key combo of
CTRL,SHIFT,ENTER:

=MIN(IF(F2:F20="",A2:A20))

Format the cell as DATE.

If there is more than one instance of the oldest date the
first instance will be returned.

Another approach:

Use conditional formatting to highlight the cell or cells
that contain the oldest date. Using this method will
highlight all instances of the oldest date:

Assume your column A dates are in the range A2:A20.

Select the range A2:A20
Goto Format>Conditional Formatting
Formula is:

=AND(A2=MIN(IF(F$2:F$20="",A$2:A$20)),F2="")

Click the Format button and select any styles you want.
OK out.

Biff
 
Both of the above suggested formulas return "01/00/1900". Here is what I'm
trying to do. On Sheet2 I have a summary which shows the:

I J
5 OLDEST FILES [ Formula in J1
1 11/19/04 DA127946 [ =LOOKUP(I1,Date.Ordered,Number)
2 11/30/04 DAA23052
3 12/03/04 DAA70412-5
4 12/06/04 DAB09503-5
5 12/10/04 8V001730

On Sheet1 I have the list:
A B F
1 DATE DATE
2 ORDERED NUMBER FILLED
3 11/19/2004 DA127946 12/10/04
4 11/30/2004 DAA23052
5 12/3/2004 DAA70412-5
6 12/6/2004 DAB09503-5 01/09/05
7 12/10/2004 DAA08872
8 12/11/2004 8V001730
9 12/15/2004 DAB11801
10 1/6/2005 DAA70654

Because F3 and F6 have dates, the order is filled. My five oldest files
(not yet filled) need to show:
1 11/30/2004
2 12/3/2004
3 12/10/2004
4 12/11/2004
5 12/15/2004

How do I get the SMALL function to see only the dates in COL-A that have a
corresponding empty cell in COL-F ??
 
Re-read the reply I gave before and follow the instructions exactly. With
the data you posted, it gives the 5 dates you list.
 
PS: To get the 5 oldest files, you need 5 formulas, that differ with respect
to the last argument to SMALL: 1, 2, 3, 4, and 5, respectively.
 
Back
Top