find closest date (number) in a range

**wamp** · 04-23-2010, 08:16 AM

in short:
I have a variable, holding a date
What I hope to do, is search for the closest match to that variable in a range with different dates, stored in range a:a.
(when found, I want to select that cell. )

variable might for example be: 21.06.09 12:13

...............A
1 18.02.09 12:34
2 20.04.09 23:59
3 22.06.09 13:00
4 22.06.09 14:30

then here it should select cell A3 since this is the closest value.

any suggestions on how to achieve this?
I've searched a bit, but can only find examples for sheet-formulas to achieve this.

I assume I have to search for the numerical value of the date(s) in the range, compare them to my variable but alas, I have no idea how to do that :-/

**StephenR** · 04-23-2010, 08:25 AM

can only find examples for sheet-formulas to achieve this

What's wrong with that? Can you post an example or two?

**DonkeyOte** · 04-23-2010, 08:31 AM

Assuming as implied that the dates are sorted then perhaps something along the lines of:

Please Login or Register  to view this content.

it dtV was say 21.05.2009 11:50 you would find A2 would be selected (closer of the adjacent values)

**wamp** · 04-23-2010, 01:50 PM

DonkeyOte: works perfect. thanks a bunch

stephenR: one example I found was

Please Login or Register  to view this content.

(whereof your search-date was put in cell E1)
Anyways.. why I want it as a macro as opposed to a sheet code, is because I want the sheet to be as dynamic as possible for the users (them not destroying the code by changing anything in the sheet.)

I could of course lock the sheet, but for some reason I have various experiences on sheet-protection in excel'97.
Even one workbook where sheet.protect didn't work, but calling a macro within the macro; running the exact same line of code DID work.

..but now I'm ramblin' on. heh.. thread marked as solved