Conditional formatting highlighting cells that contain "P" that are overdue on monthly base

[crombes]

hi everyone, i'm struggling to get this together
I want to highlight cells that contain the value "P" and are past due seen on monthly base
so all cells that have P in it from jan to sep should highlight blue and those in oct till dec not
I've tried several formulas but somehow it highlight other cells also

can anyone help

trial audit.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	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL
1			AUDITPLAN 2021
2	Q-eng Q-exp	Q-inspector	jan/21				feb/21				mrt/21				apr/21				mei/21				jun/21				jul/21				aug/21				sep/21				okt/21				nov/21				dec/21
4	dgw	DGW+LCRM		N	P	P			P			A					N					Z				A		N						P	P	P	A	P	P	A	A	A					Z	A	N					7	1	4	lcrm@picanol.be	kdvl@picanol.be
5	pcsr	PCSR+JMSS																					A																				A											1	3	5	lcrm@picanol.be	lcrm@picanol.be
6	pcsr-dgw	LNZ	P	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A				A				A						A		A				A				A				A							6		6	lcrm@picanol.be	kdvl@picanol.be			P	grijper	31/01/2021
7	JPSG	JPSG	P	P	P				A				A				Z				A				A								A				P				P				P							0									A	lucht	28/02/2021
8			grijper				lucht				grijper				lucht				grijper				lucht									grijper			lucht				grijper				lucht																		N		31/03/2021
9	DGW	LNZ	P						A										A													A																	A												Z		30/04/2021
10	DGW	LNZ+DGW	P											A								A																		A		P																					31/05/2021
11	JVLS	-					Zaman 1x OPI																													A																											30/06/2021
12	DGW	LNZ																				A																	P	A	P	N				A																	31/07/2021
13	PDRT	LNZ					A			A	A					A						A				A								A				A				A				A																	31/08/2021
14	dgw	DGW		A								A														A											A				P				A																		30/09/2021
15	dgw	DDHN	A								A								A								A									A								A																			31/10/2021
16	dgw	DDHN					A								A												A						A					P			A						A																30/11/2021
17	allen				P				P		P									P																																											31/12/2021
18		auditee	LGLL				FDPD				DDHN				SDHN				JVRB				FVDN				LGLL					FDPD			DDHN				SDHN				JVRB				FVDN
19		auditor	PCSR				DGW				LCRM				JMSS1				SDHY				PDRT				JPSG					LVDL			PCSR				DGW				SDHY				JMSS1
20	Dgw		A				A				A				A				A				A				A									A				A				A				A
21	dgw	DDHN			A				A				A				A				A					A								A				A				A			A
22	dgw	dgw				A				A				A				A				A				A									A				A				A				A							FALSE
23	dgw	dgw							A											A																								A
24	JPSG	LNZ				A				A				A				A				A				A								A				A				A				A			A
25	SDHY
26		auditor	DGW				SDHN				PCSR				DDHN				JMSS1				LGLL				SDHY					JVBR			FVDN				FDPD				LNZ				LVDL
27	PCSR	LNZ														A					A		A										A							A							A
auditkalender 2022

Cell Formulas
Range	Formula
H2,L2,P2,T2,X2,AB2,AF2,AJ2,AN2,AR2,AV2	H2	=EOMONTH(D2,0)+1
BC4	BC4	=IFERROR(COUNTIF(OFFSET($D4,,,,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$D$2:$AY$2,0)-1),"P"),"Wrong year")
BD4	BD4	=IFERROR(COUNTIF(OFFSET($D4,,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$D$2:$AY$2,0)-1,,4),"P"),"Wrong year")
BC22	BC22	=AND(MONTH(D2)<>MONTH(TODAY()),A6="P",TODAY()-BL6>0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AJ4:AM27	Expression	=AND(AJ4 ="P";MONTH($BL$14)=MONTH(TODAY()))	text	NO
AV4:AY27	Expression	=AND(AV4 ="P";MONTH($BL$17)=MONTH(TODAY()))	text	NO
AR4:AU27	Expression	=AND(AR4 ="P";MONTH($BL$16)=MONTH(TODAY()))	text	NO
AN4:AQ27	Expression	=AND(AN4 ="P";MONTH($BL$15)=MONTH(TODAY()))	text	NO
D27:AY27,D26,AB26,H26,L26,P26,T26,X26,AD26:AG26,AJ26,AV26,AN26,AR26,AY26,D4:AY25	Cell Value	="N"	text	NO
D27:AY27,D26,AB26,H26,L26,P26,T26,X26,AD26:AG26,AJ26,AV26,AN26,AR26,AY26,D4:AY25	Cell Value	="Z"	text	NO
D27:AY27,D26,AB26,H26,L26,P26,T26,X26,AD26:AG26,AJ26,AV26,AN26,AR26,AY26,D4:AY25	Cell Value	="A"	text	NO
Y5:Y7,Y9:Y16,Y20:Y25,Y27	Expression	=AND(MONTH($AJ$3)<>MONTH(TODAY());Y5="P";TODAY()-$AL$3>0)	text	NO
Y5:Y7,Y9:Y16,Y20:Y25,Y27	Expression	=AND(MONTH($AJ$3)=MONTH(TODAY());Y5="P")	text	NO
AB5:AC7,AB9:AC16,AB8,AB20:AC25,AB18:AB19,AB27:AC27,AB26	Expression	=MONTH($AB$3)=MONTH(TODAY())	text	NO
X5:AA7,X9:AA16,X8,X20:AA25,X27:AA27	Expression	=MONTH($X$3)=MONTH(TODAY())	text	NO
T5:X7,T9:X16,T8,X8,T20:X25,T27:X27	Expression	=MONTH($T$3)=MONTH(TODAY())	text	NO
P5:S7,P9:S16,P8,P20:S25,P27:S27	Expression	=MONTH($P$3)=MONTH(TODAY())	text	NO
L5:O7,L9:O16,L8,L20:O25,L27:O27	Expression	=MONTH($L$3)=MONTH(TODAY())	text	NO
H5:K7,H9:K16,H8,H20:K25,H27:K27	Expression	=MONTH($H$3)=MONTH(TODAY())	text	NO
Y4	Expression	=AND(MONTH($AJ$3)<>MONTH(TODAY());Y4="P";TODAY()-$AL$3>0)	text	NO
Y4	Expression	=AND(MONTH($AJ$3)=MONTH(TODAY());Y4="P")	text	NO
AB4:AC4	Expression	=MONTH($AB$3)=MONTH(TODAY())	text	NO
X4:AA4	Expression	=MONTH($X$3)=MONTH(TODAY())	text	NO
T4:X4	Expression	=MONTH($T$3)=MONTH(TODAY())	text	NO
P4:S4	Expression	=MONTH($P$3)=MONTH(TODAY())	text	NO
L4:O4	Expression	=MONTH($L$3)=MONTH(TODAY())	text	NO
H4:K4	Expression	=MONTH($H$3)=MONTH(TODAY())	text	NO
D4:G4	Expression	=MONTH($D$3)=MONTH(TODAY())	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D4:AY4	List	=$BJ$6:$BJ$10
AY5:AY27	List	=$BJ$6:$BJ$10
D17:AC17	List	=$BJ$6:$BJ$10
AG17:AX17	List	=$BJ$6:$BJ$10
AN12:AQ12	List	=$BJ$6:$BJ$10

 

[darkyxtreme]

Hi crombes,

Hope these work for you. I'm sure there is a more delicate way to form the formulas but as long as it works, it works :D

Please ignore row 6 as I was trying the formula to show the months < to this month.

Also keep in mind that this formula will work only on the same year, if current date is February 2022 and you want to check for October 2021, this will not work as it compares months only, not month + year.

=AND(NUMBERVALUE(TEXT(A$1,"M"))<NUMBERVALUE(TEXT(NOW(),"M")),A2="P")