If seen examples of how to do this but I'm drawing a blank.

Consider a row that has 12 columns each of which contain a date value.
Some of the dates are past, some in the future.

I two formulas: One to determine the closest future date and one to
determine the closest past date.

Can I do this with sumproduct some how?

Thanks for the help.

- John

Try these array formulas*:

For date values in A1:A20

Closest date greater than today
B1: =MIN(IF(A1:A20TODAY(),A1:A20))

Closest date less than today
C1: =MAX(IF(A1:A20<=TODAY(),A1:A20))

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"John Michl" wrote:

If seen examples of how to do this but I'm drawing a blank.

Consider a row that has 12 columns each of which contain a date value.
Some of the dates are past, some in the future.

I two formulas: One to determine the closest future date and one to
determine the closest past date.

Can I do this with sumproduct some how?

Thanks for the help.

- John

Ron,
That works like a charm. I dabled with both array and sumproduct
formulas but quickly created something more complex than your
relatively simple solution. Thanks a ton.

- John

You're very welcome, John...I'm glad you could use those formulas.

***********
Regards,
Ron

XL2002, WinXP-Pro


"John Michl" wrote:

Ron,
That works like a charm. I dabled with both array and sumproduct
formulas but quickly created something more complex than your
relatively simple solution. Thanks a ton.

- John