Conditional Formatting for Comparing Dates

[94mustang]

Hello Excel Experts,

I am needing much help creating a conditional formatting custom formula to highlight the intersecting cells. See the attached example screenshot. Take a look at the 4 dates for Facility 15 (Row 81) and I have manually highlighted each corresponding dates that match the intersection of the header dates. I just want to be able to enter dates for each facility in the Date1 to Date4 columns and the corresponding intersecting match date to be highlighted with a background color. I will have many more facilities to add but just wanted to give a good representative example in this thread of what I am trying to accomplish.

I think it might be an array formula to accomplish this but not confident. I have been trying to use the month and year function of date1 to date4 to match the month and year of the header dates with no success. Any help will be greatly appreciated. Thank you.

 

[Peter_SSs]

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’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

My dates are in d/m/y format and I'm unsure of your column range but see if this will work for you.

23 01 08.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
80					1/01/23	1/02/23	1/03/23	1/04/23	1/05/23	1/06/23	1/07/23	1/08/23	1/09/23	1/10/23	1/11/23	1/12/23	1/01/24	1/02/24	1/03/24	1/04/24	1/05/24	1/06/24	1/07/24	1/08/24	1/09/24	1/10/24	1/11/24	1/12/24
81	25/01/2023	17/05/2023	9/08/2024	8/10/2025
82	15/03/2023	21/09/2023
83
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F81:AC85	Expression	=MATCH(F$80,$B81:$AZ81-DAY($B81:$AZ81)+1,0)	text	NO

 

[94mustang]

Hello Peter_SSs,

It has been a few years since I have been on this forum. My company recently converted from Google to Microsoft. Thank you so much for your advice as I have already visited my account and updated my profile. I have also click on the link for the minisheets so the next time I have a question, I will use this as well. Finally, your post contained the solution to my problem. This is amazing. Thank you so very much for your help. How do I give you credit or stars in this forum?

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.
Also for updating your details.

How do I give you credit or stars in this forum?

You mark the answer that solved your question (which you have already done). If you want you can also click the 'Like' icon at the bottom right of any post but those 'Likes' don't get used for anything.