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

=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"
wrote in message
news:edwardpestian.28x7ka_1149496501.8576@excelfor um-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

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

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"
wrote in message
...

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

Thanks, does just what I need.

Regards,

EP


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=548491

On Mon, 5 Jun 2006 03:30:02 -0500, edwardpestian
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

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"
wrote in message
...

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