2007 - Pulling date from a date range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shaunholtz21@hotmail.com - 23 Jul 2007 16:23 GMT

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

Reply to this Message

Rick Rothstein (MVP - VB) - 23 Jul 2007 16:51 GMT

> 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
[quoted text clipped - 3 lines]
> I need to be able to pull the date next closest or equal to today.
> Can anyone help me out?

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

Reply to this Message

shaunholtz21@hotmail.com - 23 Jul 2007 17:06 GMT

On Jul 23, 10:51 am, "Rick Rothstein \(MVP - VB\)"
<rickNOSPAMn...@NOSPAMcomcast.net> wrote:

> > 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
[quoted text clipped - 11 lines]
>
> Rick

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.

Reply to this Message

Peo Sjoblom - 23 Jul 2007 17:24 GMT

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

Signature

Regards,

Peo Sjoblom

> On Jul 23, 10:51 am, "Rick Rothstein \(MVP - VB\)"
> <rickNOSPAMn...@NOSPAMcomcast.net> wrote:
[quoted text clipped - 27 lines]
> Ideally, the cell with the formula, should pull the 8/2/2007 date for
> right now.

Reply to this Message

Rick Rothstein (MVP - VB) - 23 Jul 2007 17:48 GMT

>> > 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
[quoted text clipped - 25 lines]
> Ideally, the cell with the formula, should pull the 8/2/2007 date for
> right now.

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

Reply to this Message