Need help finding nearest date from list of dates

[Pcec21]

I need a some help with a formula that takes the date from column A looks at all the dates in column B and spits out the nearest date from Column B into Column C. I have been using this formula =INDEX($B$1:$B$12,MATCH(A1+1,$B$1:$B$12,1)) and it will not always give me the nearest date.






Column A Column B
10/23/2017 10/25/2017
10/24/2017 10/27/2017
10/25/2017 11/3/2017
10/27/2017 11/10/2017
10/30/2017 11/14/2017
10/31/2017 11/20/2017
11/1/2017 11/24/2017
11/2/2017 11/28/2017
11/3/2017 12/05/2017
11/6/2017 12/11/2017
11/7/2017 12/22/2017
11/7/2017
11/8/2017
11/9/2017
11/14/2017
11/15/2017
11/16/2017
11/20/2017
11/21/2017
11/22/2017
11/23/2017
11/24/2017
11/27/2017
11/28/2017
11/29/2017
11/30/2017
12/1/2017
12/4/2017
12/5/2017
12/6/2017
12/7/2017
12/8/2017
12/12/2017
12/13/2017
12/14/2017
12/18/2017
12/20/2017
12/21/2017
12/22/2017

[AliGW]

What do you mean? Perhaps you could be more explicit about when it fails?

Welcome, by the way!

[Pcec21]

The formula fails sense that it always produces the date that is before it unless that date is one day after and picks up the +1 in the formula.

[MrShorty]

How do you intend to sort out "nearest date" for say 11/22/17 which is two days from 11/20/17 and 11/24/17?

I would probably change the lookup table so that it gives the correct result. Assuming something like 11/22/17 can be matched with 11/24/17, I might add a helper column =AVERAGE(B2:B3) in E3 [note relative reference for a moving average], then copy down to get the midpoints of the list of dates in column B. I would probably put an earliest data (10/1/17???) into E2 to avoid the N/A errors that the earlier dates will give. Then use that as the lookup column for the MATCH() function MATCH(A2,$E$2:$E$12,1)

[63falcondude]

I moved column B to column D for clarity.

If by "nearest" you mean past, present, or future, try this in B2:

=INDEX(D$2:D$12,MATCH(MIN(ABS(D$2:D$12-A2)),ABS(D$2:D$12-A2),0)) Ctrl Shift Enter

See attachment for clarification.

Edit: MrShorty brings up a good point about dates that are the same distance away (future and past). I did not consider that.

[Pcec21]

The issue I have is that i need column C to read an exact date from column B, the formula still points to the earlier date in column B

[daddylonglegs]

Hello Pcec21,

I just read this thread for the first time and I don’t think you’ve really explained what you want to do. Can you give some concrete examples? For example, given the data in post 1 what result do you expect to get in C1, C2, C3 etc?

[Pcec21]

Sorry I had to step away from my desk for a moment. The formula that 63falcondude gave worked perfectly! I am very new to this and I greatly appreciate all the help.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[63falcondude]

Great, glad we could help.