finding a date/time in a list that is closest to an existing date/

[Jamie]

I have a list of dates and times. They are all on 15 min intervals. I have
another list of dates and times that are to the nearest minute.They aren't on
a regular interval so one time might be 2006-01-01 23:59 and the next might
be 2006-03-17 15:10. I want to check the dates in the first column(15min
intervals) against the second column. I would like the formula to recognize
2006-03-17 15:15 as the closest value to 2006-03-17 15:10 so that the formula
can take the data from next to the 15:10 and put it next 15:15 time.

Thanks

 

[edcosoft]

Pressuming your date columns are 52 rows long and the odd intervals are
in ascending order in column A with their data in column B and the 15
minute intervals are in column C, put the following formula in cell
D-1. If column A is not in ascending order use the Sort command from
the Data menu and be sure to sort only column B along with A.

=VLOOKUP(C1,$A$1:$D$52,2)

then copy cell cell D-1 down through D-52


ed