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

New Member
Joined
Oct 25, 2011
Messages
13
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Hello

Welcome to the message board!

Try this array (control-shift-enter) formula:

<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 /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">10/28/2011</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">10/24/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">10/23/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">10/1/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">10/28/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10/29/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">10/30/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">10/31/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">11/1/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">11/2/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">11/3/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">11/4/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">11/5/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">11/6/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">11/7/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">11/8/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">11/9/2011</td><td style="text-align: right;;"></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">{=MIN(<font color="Blue">IF(<font color="Red">E7:E22>TODAY(<font color="Green"></font>),E7:E22</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

New Member
Joined
Oct 25, 2011
Messages
13
Hello Jeff.
Im pretty new at excel. Do i just type in "=MIN(IF(E7:E22>TODAY(),E7:E22))" IN THE F4 CELL. I dont really know what the crl shift enter formula means. Can you please explain in dummy terms. thank you!
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
yes type or paste that in... now here is the key:


It simply means to hold the control key and shift key down while you press enter.

These three keys signal to excel that this is an array formula.

Just place your curser in the formula, then hold control, shift, enter.


Give it a try and let me know if you see the curly brackets appear {}
 

Airman

New Member
Joined
Oct 25, 2011
Messages
13

ADVERTISEMENT

Okay so i figured how to do the array formula. But now i have another problem. Some of the dates are past due so can i have the past due date show up in F4 instead of the up coming date?
 

Airman

New Member
Joined
Oct 25, 2011
Messages
13

ADVERTISEMENT

So in the E column i have say a set of dates: 23 oct 11, 21 oct 11, 01 nov 11, n/a and so on and so forth. I want F4 to say 21 oct 11. So that i know it is past due and an inspection needs to be done. And than when an inspection is done the date of 21 oct will change to whatever the new due date will be so say 21 nov 11 now i want the f4 to automatically update to 23 oct 11.
 

Airman

New Member
Joined
Oct 25, 2011
Messages
13
omg it worked. Thank you i really appricate it. Im not very computer savvy and i was tasked with this project. Airman all the way! again thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,422
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top