get closest date to today & not prior to today?

Sam F

New Member
Joined
Nov 1, 2011
Messages
7
<style> <!-- /* Font Definitions */ @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Cambria; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> I’ve got a sheet I’ve made to organize for film festivals and their various submission deadlines. What I want is something that that takes the current date and shows the closest deadline that hasn’t already past. My deadlines are horizontal and ascending (example 12-3-11, 1-3-12, 1-21-12, etc).
I’ve looked around and tried some HLOOKUP & INDEX options, but get either errors or strange results. Can someone please help? Thanks!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Welcome to the board.

Is it possible to rearrange the data so those dates are sorted DEscending?
 
Upvote 0
Thanks for the response! I'd really rather not change the order to descending as that would make viewing it for all other purposes very hard and prone to error. The way I have it is: Early Deadline, Regular Deadline, Late Deadline, then Extended Deadline. If I reverse that, I'll probably mess up deadlines and input them incorrectly as well.

Is there any other option?
 
Upvote 0
Thanks for the response! I'd really rather not change the order to descending as that would make viewing it for all other purposes very hard and prone to error. The way I have it is: Early Deadline, Regular Deadline, Late Deadline, then Extended Deadline. If I reverse that, I'll probably mess up deadlines and input them incorrectly as well.

Is there any other option?
Something like this...

Array entered**:

=MIN(IF(A1:J1>=TODAY(),A1:J1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

Assumes there will be at least 1 date that meets the criteria.
 
Upvote 0
Thanks! That works a charm. What would make it perfect would be shade the cell red if the deadline is w/i 5 days. Any thoughts on how to add that?

I found this formula =IF(A3<(TODAY()+5),”ALERT: DUE DATE”,””) and I believe you're supposed to use conditional formatting for color coding like this, but I don't know how to do any of this or how to put it together.

Again, any help is greatly appreciated!
 
Upvote 0
Thanks! That works a charm. What would make it perfect would be shade the cell red if the deadline is w/i 5 days. Any thoughts on how to add that?

I found this formula =IF(A3<(TODAY()+5),”ALERT: DUE DATE”,””) and I believe you're supposed to use conditional formatting for color coding like this, but I don't know how to do any of this or how to put it together.

Again, any help is greatly appreciated!
What version of Excel are you using?

So, the criteria would be:
  • the date is >=today's date and
  • <=today's date + 5 days
Is that correct?
 
Upvote 0
Thanks. I'm using 2008 for Mac. I have 2007 on the PC but it's not nearly as convenient to get to. And yes, I believe you have the criteria correct. Is this possible?

Thanks!
 
Upvote 0
Thanks. I'm using 2008 for Mac. I have 2007 on the PC but it's not nearly as convenient to get to. And yes, I believe you have the criteria correct. Is this possible?

Thanks!
OK, I have no idea about Mac Excel.

Here's how to do it in Excel 2007 on a pc...

Let's assume the range of dates is A1:J1

Select the *entire* range A1:J1 starting from cell A1.
Cell A1 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=AND(A1>=TODAY(),A1<=TODAY()+5)

Click the Format button
Select the desired style(s)
OK out
 
Upvote 0
Wow! That worked great! I actually didn't select the range as you mentioned, but, since I made a column for "Closest Deadline" just the cell in that column. After that worked, I selected the whole column and did the formatting. Again, success! What a time saver. I really appreciate it!!

The only other thing I'm trying to do, which isn't a big deal, is to add a leave blank command to the formula. So the cell formula looks like this:

<style> <!-- /* Font Definitions */ @font-face {font-family:Arial; panose-1:2 11 6 4 2 2 2 2 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Cambria; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> =MIN(IF(E17:H17>=TODAY(),E17:H17))
and my blank attempt is:
<style> <!-- /* Font Definitions */ @font-face {font-family:Arial; panose-1:2 11 6 4 2 2 2 2 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Cambria; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> =IF(E17:H17="","",MIN(IF(E17:H17>=TODAY(),E17:H17))
This seems to wreck things somehow. Pretty small matter though. Was only doing as a shortcut so I won't have to copy the formula to each cell, but that doesn't take very long.



 
Upvote 0
Wow! That worked great! I actually didn't select the range as you mentioned, but, since I made a column for "Closest Deadline" just the cell in that column. After that worked, I selected the whole column and did the formatting. Again, success! What a time saver. I really appreciate it!!

The only other thing I'm trying to do, which isn't a big deal, is to add a leave blank command to the formula. So the cell formula looks like this:

<STYLE> <!-- /* Font Definitions */ @font-face {font-family:Arial; panose-1:2 11 6 4 2 2 2 2 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Cambria; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </STYLE>=MIN(IF(E17:H17>=TODAY(),E17:H17))
and my blank attempt is:
<STYLE> <!-- /* Font Definitions */ @font-face {font-family:Arial; panose-1:2 11 6 4 2 2 2 2 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Cambria; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </STYLE>=IF(E17:H17="","",MIN(IF(E17:H17>=TODAY(),E17:H17))
This seems to wreck things somehow. Pretty small matter though. Was only doing as a shortcut so I won't have to copy the formula to each cell, but that doesn't take very long.
Ok, are you trying to say: If there are NO dates in the range then return a blank?

If so, try it like this...

=IF(COUNT(E17:H17),MIN(IF(E17:H17>=TODAY(),E17:H17)),"")

Don't forget to array enter!
 
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,070
Members
453,593
Latest member
Mubashar Ali

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
Back
Top