Cross referencing dates

[Nharsh]

I'm trying to set up data table to conditionally format a calendar. The calendar I was trying to use is a formula based one that adjusts the dates and shifts weekdays as the year changes. What I want to do is have a data table to enter specific dates and have the calendar be conditionally formatted and color the dates that are entered into the table. I'm new to excel and I'm not even sure if can be done considering the calendar that I want to use is already completely formulas.

 

[Toadstool]

Hi Nharsh,

It's impossible to give you a complete solution without knowing the contents of your sheet(s) and which columns are used. Here's an example with a few dates for FTP (which I see is in 2 columns so that's a complication which means I need 2 MATCH statements to check if the date is found) and just January where I assume the date shown is formatted as "d" to just show the day number. My example shows matching dates as beige.

I'm guessing the calendar already has at least one Conditional Format (CF) so days in other months are shown in light grey so I've added that as an example. NOTE: In the Conditional Format I've put the greying out CF first and selected "Stop if true" so it doesn't then highlight a matching date but in the wrong month (e.g. 2nd and 3rd February in my example).

NHarsh.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		FTPs										2021
2		Completed	Paid			Completed	Paid					January
3	1	04-Jan-21			49	25-Jan-21			Mon	Tue	Wed	Thu	Fri	Sat	Sun
4	2	04-Jan-21			50	29-Jan-21			28	29	30	31	1	2	3
5	3	07-Jan-21			51	02-Feb-21			4	5	6	7	8	9	10
6	4	11-Jan-21			52	03-Feb-21			11	12	13	14	15	16	17
7	5	13-Jan-21			53				18	19	20	21	22	23	24
8	6	21-Jan-21			54				25	26	27	28	29	30	31
9	7	22-Jan-21			55				1	2	3	4	5	6	7
10	8	22-Jan-21			56
11	9	22-Jan-21			57
12	10	22-Jan-21			58
Sheet1

Cell Formulas
Range	Formula
O4:T4	O4	=N4+1
N5:T9	N5	=N4+7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N4:T9	Expression	=MONTH(N4)<>MONTH($Q$2)	text	YES
N4:T9	Expression	=OR(ISNUMBER(MATCH(N4,$G$3:$G$50,0)),ISNUMBER(MATCH(N4,$K$3:$K$26,0)))	text	NO

...and here's a couple of screenshots to show how the CF is set and the sequence of the two CFs in this example.