date formula

[edwardpestian]

I'm having trouble with a date formula. What I have is a column listing the date, every two weeks. 05/14/2006, 05/28/2006, 06/11/2006, etc. I need to lookup in that range, the cell containing the date that is less than today.

In other words if today is less than 06/11/2006, then move up one cell to 05/28/2006. Or if today is less than 05/28/2006, then move up one cell to 05/14/2006.

Thanks in advance.

EP

[Bob Phillips]

=MIN(IF(A1:A100<TODAY(),A1:A100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"edwardpestian" <edwardpestian.28x7ka_1149496501.8576@excelforum-nospam.com>
wrote in message
news:edwardpestian.28x7ka_1149496501.8576@excelforum-nospam.com...
>
> I'm having trouble with a date formula. What I have is a column listing
> the date, every two weeks. 05/14/2006, 05/28/2006, 06/11/2006, etc. I
> need to lookup in that range, the cell containing the date that is less
> than today.
>
> In other words if today is less than 06/11/2006, then move up one cell
> to 05/28/2006. Or if today is less than 05/28/2006, then move up one
> cell to 05/14/2006.
>
> Thanks in advance.
>
> EP
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=548491
>

[edwardpestian]

The formula seems to find the earliest date in the range as opposed to the latest date less than today. If I have 06/01/2006, 06/02/2006 and 06/03/2006, and today is the 06/10/2006, the formula should return 06/03/2006.

Thanks Again.

EP

[Heather Heritage]

Can't see the original formula, but the array formula

=MAX(IF(D2:D4<TODAY(),D2:D4,0))

replace D2:D4 with the actual range you want to use, and press Ctrl Shift
Enter to get the value. If no dates match, you will get a Zero returned.
"edwardpestian" <edwardpestian.28xasy_1149500702.38@excelforum-nospam.com>
wrote in message
news:edwardpestian.28xasy_1149500702.38@excelforum-nospam.com...
>
> The formula seems to find the earliest date in the range as opposed to
> the latest date less than today. If I have 06/01/2006, 06/02/2006 and
> 06/03/2006, and today is the 06/10/2006, the formula should return
> 06/03/2006.
>
> Thanks Again.
>
> EP
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=548491
>

[edwardpestian]

Thanks, does just what I need.

Regards,

EP

[Ron Rosenfeld]

On Mon, 5 Jun 2006 03:30:02 -0500, edwardpestian
<edwardpestian.28x7ka_1149496501.8576@excelforum-nospam.com> wrote:

>
>I'm having trouble with a date formula. What I have is a column listing
>the date, every two weeks. 05/14/2006, 05/28/2006, 06/11/2006, etc. I
>need to lookup in that range, the cell containing the date that is less
>than today.
>
>In other words if today is less than 06/11/2006, then move up one cell
>to 05/28/2006. Or if today is less than 05/28/2006, then move up one
>cell to 05/14/2006.
>
>Thanks in advance.
>
>EP

A simple VLOOKUP formula should do that:

=VLOOKUP(TODAY(),DateRangeList,1)


--ron

[Bob Phillips]

then just use MAX

=MAX(IF(A1:A100<TODAY(),A1:A100))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"edwardpestian" <edwardpestian.28xasy_1149500702.38@excelforum-nospam.com>
wrote in message
news:edwardpestian.28xasy_1149500702.38@excelforum-nospam.com...
>
> The formula seems to find the earliest date in the range as opposed to
> the latest date less than today. If I have 06/01/2006, 06/02/2006 and
> 06/03/2006, and today is the 06/10/2006, the formula should return
> 06/03/2006.
>
> Thanks Again.
>
> EP
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=548491
>