2007 - Pulling date from a date range

  • Thread starter Thread starter shaunholtz21
  • Start date Start date
S

shaunholtz21

Hi! -

I was hoping someone could help me please. Here's what I have. A
worksheet that has several dates in a row. D1:T1, Of that range, I
have conditional formatting set to highlight a date(s) either = to or
within the next 7 days.

I need to be able to pull the date next closest or equal to today.
Can anyone help me out?

Any help would be greatly appriecated.

Shaun
 
I was hoping someone could help me please. Here's what I have. A
worksheet that has several dates in a row. D1:T1, Of that range, I
have conditional formatting set to highlight a date(s) either = to or
within the next 7 days.

I need to be able to pull the date next closest or equal to today.
Can anyone help me out?
Click to expand...

I think this does what you want...

=TODAY()+MIN((IF(D1:T1>=TODAY(),1,-9999))*(D1:T1-TODAY()))

NOTE: This is an array formula and must be entered using Ctrl+Shift+<Enter>.

Rick
 
I think this does what you want...

=TODAY()+MIN((IF(D1:T1>=TODAY(),1,-9999))*(D1:T1-TODAY()))

NOTE: This is an array formula and must be entered using Ctrl+Shift+<Enter>.

Rick
Click to expand...

Rick, Thanks for your help.

I insert the formula and come back with the date 11/13/3266

The dates I have from D1:T1 are as follows....

Starting with D1 5/32007, E1 5/6/2007, 5/10/2007, 5/17/2007,
5/20/2007, 5/31/2007, 6/7/2007,6/21/2007,6/24/2007, 7/8/2007,
7/12/2007, 7/15/2007, 7/19/2007, 7/22/2007, 8/2/2007, T1 8/5/2007.

Ideally, the cell with the formula, should pull the 8/2/2007 date for
right now.
 
Try this

=INDEX(D1:T1,MATCH(SMALL(D1:T1,COUNTIF(D1:T1,"<"&TODAY())+1),D1:T1,0))

format result as date or you'll get the serial number for 8/2/07 which is
39296
 
I was hoping someone could help me please. Here's what I have. A
Rick, Thanks for your help.

I insert the formula and come back with the date 11/13/3266

The dates I have from D1:T1 are as follows....

Starting with D1 5/32007, E1 5/6/2007, 5/10/2007, 5/17/2007,
5/20/2007, 5/31/2007, 6/7/2007,6/21/2007,6/24/2007, 7/8/2007,
7/12/2007, 7/15/2007, 7/19/2007, 7/22/2007, 8/2/2007, T1 8/5/2007.

Ideally, the cell with the formula, should pull the 8/2/2007 date for
right now.
Click to expand...

I'm not sure what to tell you... I just tried my formula again (pasted it
from my posting just to be sure) with the dates you posted (correcting the
first one's typo and providing a made-up date for the one you missed) and
the formula returned 8/2/2007 as expected. Did you copy/paste the formula
from my post in order to make sure you didn't introduce any typos by trying
to do it manually? I'm sure you used the Ctrl+Shift+<Enter> method to
activate the formula because otherwise you would have gotten a #VALUE!
error, so that can't be the problem. The only thing I can suggest is to try
copy/pasting it again and see if that corrects the problem.

Rick
 
Back
Top