Highlighting nearest date

[Ian1976]

Hi I have a table which holds dates (G17:G23) is there anyway of highlighting the nearest date in the corresponding row to the date shown in column G?

Thank you, its part of a much bigger spreadsheet or I'd do it manually!

28/09/2021		23/07/2021	17/09/2021	12/11/2021	07/01/2022	04/03/2022
21/07/2021		03/07/2021	28/08/2021	23/10/2021	18/12/2021	12/02/2022
04/07/2021		11/07/2021	05/09/2021	31/10/2021	26/12/2021	20/02/2022
22/08/2021		02/07/2021	27/08/2021	22/10/2021	17/12/2021	11/02/2022
15/10/2021		29/07/2021	23/09/2021	18/11/2021	13/01/2022	10/03/2022
14/07/2021		22/07/2021	16/09/2021	11/11/2021	06/01/2022	03/03/2022
20/07/2021		09/07/2021	03/09/2021	29/10/2021	24/12/2021	18/02/2022

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also which of those columns is col G?

 

[KOKOSEK]

CF for A1:

=MIN(ABS($A1:$F1-$G1))=ABS(A1-$G1)

set colour or whatever you want then apply this CF into A1:G7

p.s. I am not sure that I understand correctly because in you example always date from F is nearest, coincidence?

 

[Fluff]

Assuming that the first col is col G, how about

+Fluff 1.xlsm
	G	H	I	J	K	L	M
17	28/09/2021		23/07/2021	17/09/2021	12/11/2021	07/01/2022	04/03/2022
18	21/07/2021		03/07/2021	28/08/2021	23/10/2021	18/12/2021	12/02/2022
19	04/07/2021		11/07/2021	05/09/2021	31/10/2021	26/12/2021	20/02/2022
20	22/08/2021		02/07/2021	27/08/2021	22/10/2021	17/12/2021	11/02/2022
21	15/10/2021		29/07/2021	23/09/2021	18/11/2021	13/01/2022	10/03/2022
22	14/07/2021		22/07/2021	16/09/2021	11/11/2021	06/01/2022	03/03/2022
23	20/07/2021		09/07/2021	03/09/2021	29/10/2021	24/12/2021	18/02/2022
Lists

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I17:M23	Expression	=ABS($G17-I17)=MIN(ABS($I17:$M17-$G17))	text	NO

 

[Ian1976]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also which of those columns is col G?

Hi Fluff,

Sorry details updated and column G is the 1st column of the table!

Thanks

 

[Fluff]

Thanks for that. Have a look at post#4

 

[KOKOSEK]

That's effect of insufficient details

 

[Fluff]

That's effect of insufficient details

You could always have asked, or waited until the OP replied to me.

 

[Ian1976]

hi Fluff,

Working great, but one question....

When I use these dates I'm getting 2 cells highlighted??

The 09/09/21 & 04/11/21 highlight.

Thanks again!

@KOKOSEK Thanks for the help, I kind of threw you a curve ball!

07/10/2021

15/07/2021

09/09/2021

04/11/2021

30/12/2021

24/02/2022

21/04/2022

16/06/2022

 

[Joe4]

When I use these dates I'm getting 2 cells highlighted??

Because they are both EXACTLY 28 days from your date.
So it is a TIE.