Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need to find oldest date in ever changing list.
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? -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
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. On Thu, 17 Feb 2005 23:55:04 GMT, "Alan Anderson via OfficeKB.com" wrote: 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? |
#3
|
|||
|
|||
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 FormatConditional 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 -----Original Message----- 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? -- Message posted via http://www.officekb.com . |
#4
|
|||
|
|||
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 ?? -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
Re-read the reply I gave before and follow the instructions exactly. With
the data you posted, it gives the 5 dates you list. "Alan Anderson via OfficeKB.com" wrote in message ... 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 ?? -- Message posted via http://www.officekb.com |
#6
|
|||
|
|||
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. "Alan Anderson via OfficeKB.com" wrote in message ... 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 ?? -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find duplicates in a list | Excel Discussion (Misc queries) | |||
Detecting Oldest Date On Spreadsheet | Excel Discussion (Misc queries) | |||
How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) | |||
How can I sum information in a list with a date range? | Excel Worksheet Functions | |||
find date in Col A corresponding to min value in Col B | Excel Worksheet Functions |