How to make excel sort through a column of dates and automatically find the most upcoming date and enter it into a different cell

[Airman]

I have E7 through E22 with due dates. Some of the cell in the E column doesnt not have a date entered just "n/a". And i want excel to go through the dates that are in the E column and automatically find the one that is upcoming up the soonest and have it automatically entered in F4. Is there a way to do this?

 

[Airman]

okay maybe i spoke to soon. that formula works when there is an item past due and it will pick out the most outdated item. But if i inspect all the outdated items and now all the items are current as of say 01 nov. The F4 cell wont update to 01 Nov 11. Since there are no dates past due anymore. Is there another formula to make all of this happen?

 

[repairman615]

I think so:

=IF(MIN(IF(E7:E22<TODAY(),E7:E22))=0,MIN(IF(E7:E22>=TODAY(),E7:E22)),MIN(IF(E7:E22<TODAY(),E7:E22)))

ctrl+shift+enter

 

[repairman615]

=if(min(if(e7:e22 < today(),e7:e22))=0,min(if(e7:e22 > =today(),e7:e22)),min(if(e7:e22 < today(),e7:e22)))


posting trouble... remove all the spaces

 

[Airman]

It doesnt work.

 

[repairman615]

some times the while posting <> signs, the message board thinks it is meant as < html > ... try the previous

 

[Airman]

It just says True.

 

[repairman615]

I am getting results. copy and paste to double check:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">10/1/2011</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">{=IF(<font color="Blue">MIN(<font color="Red">IF(<font color="Green">E7:E22<TODAY(<font color="Purple"></font>),E7:E22</font>)</font>)=0,MIN(<font color="Red">IF(<font color="Green">E7:E22>=TODAY(<font color="Purple"></font>),E7:E22</font>)</font>),MIN(<font color="Red">IF(<font color="Green">E7:E22<TODAY(<font color="Purple"></font>),E7:E22</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Airman]

YAY it works. Thank you again. i probably typed it in wrong. but it works now.

 

[repairman615]

your welcome

 

[KIRAN VARMA]

I have E7 through E22 with due dates. Some of the cell in the E column doesnt not have a date entered just "n/a". And i want excel to go through the dates that are in the E column and automatically find the one that is upcoming up the soonest and have it automatically entered in F4. Is there a way to do this?

In F4
=SUBTOTAL(4,E7:E22)