Time Clash formula not working

[eli_m]

Hi,

I currently have this data:

I have both these conditional formatting to show red::
=AND(COUNTIF($AY$3:$AY$200,AY3)>1,NOT($AY3=""),COUNTIF($AQ3,"Planning Time Approved*")=0,$AX3="No")
=AND(ROW()>1,ABS($V3-$V2)*1440<29,$AX3="No",COUNTIF($AQ3,"Planning Time Approved*")=0)

for both V and AY columns.

Why does this not show up as RED?

It shows RED for another row but it is not correct as the dates are not the same:

Any help/reworking would be greatly appreicated.

Thanks!

 

[Peter_SSs]

Why does this not show up as RED?

It shows RED for another row but it is not correct as the dates are not the same:

Well, for one thing, the formulas refer to columns AX and AQ which we cannot see.

Generally when unusual rows show up with Conditional Formatting it is because the cells selected and the formula applied when the CF is set up do not match.
For example, if you had all of column AY selected when you applied this CF formula =AND(COUNTIF($AY$3:$AY$200,AY3)>1,NOT($AY3=""),COUNTIF($AQ3,"Planning Time Approved*")=0,$AX3="No") then everything will be off by 2 rows because the active cell was AY1 but the formula refers to AY3 & AQ3.

We cannot tell much from a picture. If still problems, post the samples again with XL2BB

 

[eli_m]

Well, for one thing, the formulas refer to columns AX and AQ which we cannot see.

Generally when unusual rows show up with Conditional Formatting it is because the cells selected and the formula applied when the CF is set up do not match.
For example, if you had all of column AY selected when you applied this CF formula =AND(COUNTIF($AY$3:$AY$200,AY3)>1,NOT($AY3=""),COUNTIF($AQ3,"Planning Time Approved*")=0,$AX3="No") then everything will be off by 2 rows because the active cell was AY1 but the formula refers to AY3 & AQ3.

We cannot tell much from a picture. If still problems, post the samples again with XL2BB

Thanks for the tip - I have uploaded the XL2BB:

Book1.xlsx
	U	V																					AQ							AX	AY
1	Planning Date	Planning Time																					Notes:							Planning Passed	Planning Clashes - Check for 29 Mins
2	Example Row - Blank	Example Row - Blank																												Example Row - Blank	Example Row - Blank
3	9/08/2023	13:10																												No	09/08/23 01:10 PM
4	24/09/2023	13:30																												No	24/09/23 01:30 PM
5	24/09/2023	13:15																												No	24/09/23 01:15 PM
6	28/08/2023	13:30																												No	28/08/23 01:30 PM
7	8/08/2023	13:30																												No	08/08/23 01:30 PM
8	13/09/2023	14:00																					Planning Time Approved by Engineering							No	13/09/23 02:00 PM
9	21/09/2023	8:30																												No	21/09/23 08:30 AM
Sheet1

Cell Formulas
Range	Formula
AX3:AX9	AX3	=IFERROR( IF(ISNUMBER([@[Planning Date]]),IF(NOW()>[@[Planning Date]]+[@[Planning Time]],"Yes","No"), ""), "")
AY3:AY9	AY3	=IF([@[Planning Date]]="N/A","", IF(ISFORMULA([@[Planning Time]]),"", CONCATENATE(TEXT([@[Planning Date]],"dd/mm/yy")," ",TEXT([@[Planning Time]],"hh:mm am/pm"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AY4:AY9	Expression	=AND(ROW()>1,ABS($V4-$V3)*1440<29,$AX4="No",COUNTIF($AQ4,"Planning Time Approved*")=0)	text	NO
AY4:AY9	Expression	=AND(ROW()>1,ABS($V5-$V4)*1440<29,$AX5="No",COUNTIF($AQ5,"Planning Time Approved*")=0)	text	NO
AY3	Expression	=AND(ROW()>1,ABS($V3-#REF!)*1440<29,$AX3="No",COUNTIF($AQ3,"Planning Time Approved*")=0)	text	NO
AY3	Expression	=AND(ROW()>1,ABS($V4-$V3)*1440<29,$AX4="No",COUNTIF($AQ4,"Planning Time Approved*")=0)	text	NO

I am unsure why AY3, AY6 & AY7 is RED.

I just want it to turn red if theres the same date and time 29 minutes from each other

Thanks in advance

 

[Peter_SSs]

I just want it to turn red if theres the same date and time 29 minutes from each other

None of your CF formulas take account of the date (col U).

See if this works for you
Delete the CF in column AY
Select AY3 down to the end of column AY in the table (not the entire column AY)
Apply this CF

eli_m.xlsm
	U	V																					AQ							AX	AY
1	Planning Date	Planning Time																					Notes:							Planning Passed	Planning Clashes - Check for 29 Mins
2	Example Row - Blank	Example Row - Blank																												Example Row - Blank	Example Row - Blank
3	9/08/2023	13:10																												No	09/08/23 01:10 PM
4	24/09/2023	13:30																												No	24/09/23 01:30 PM
5	24/09/2023	13:15																												No	24/09/23 01:15 PM
6	28/08/2023	13:30																												No	28/08/23 01:30 PM
7	8/08/2023	13:30																												No	08/08/23 01:30 PM
8	13/09/2023	14:00																					Planning Time Approved by Engineering							No	13/09/23 02:00 PM
9	21/09/2023	8:30																												No	21/09/23 08:30 AM
10
11
Sheet1

Cell Formulas
Range	Formula
AX3:AX9	AX3	=IFERROR( IF(ISNUMBER([@[Planning Date]]),IF(NOW()>[@[Planning Date]]+[@[Planning Time]],"Yes","No"), ""), "")
AY3:AY9	AY3	=IF([@[Planning Date]]="N/A","", IF(ISFORMULA([@[Planning Time]]),"", CONCATENATE(TEXT([@[Planning Date]],"dd/mm/yy")," ",TEXT([@[Planning Time]],"hh:mm am/pm"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AY3:AY9	Expression	=OR(AND(ABS(($U3+$V3)-($U2+$V2))*1440<29,$AX3="No",COUNTIF($AQ3,"Planning Time Approved*")=0),AND(ABS(($U4+$V4)-($U3+$V3))*1440<29,$AX4="No",COUNTIF($AQ4,"Planning Time Approved*")=0))	text	NO

 

[eli_m]

None of your CF formulas take account of the date (col U).

See if this works for you
Delete the CF in column AY
Select AY3 down to the end of column AY in the table (not the entire column AY)
Apply this CF

eli_m.xlsm
	U	V																					AQ							AX	AY
1	Planning Date	Planning Time																					Notes:							Planning Passed	Planning Clashes - Check for 29 Mins
2	Example Row - Blank	Example Row - Blank																												Example Row - Blank	Example Row - Blank
3	9/08/2023	13:10																												No	09/08/23 01:10 PM
4	24/09/2023	13:30																												No	24/09/23 01:30 PM
5	24/09/2023	13:15																												No	24/09/23 01:15 PM
6	28/08/2023	13:30																												No	28/08/23 01:30 PM
7	8/08/2023	13:30																												No	08/08/23 01:30 PM
8	13/09/2023	14:00																					Planning Time Approved by Engineering							No	13/09/23 02:00 PM
9	21/09/2023	8:30																												No	21/09/23 08:30 AM
10
11
Sheet1

Cell Formulas
Range	Formula
AX3:AX9	AX3	=IFERROR( IF(ISNUMBER([@[Planning Date]]),IF(NOW()>[@[Planning Date]]+[@[Planning Time]],"Yes","No"), ""), "")
AY3:AY9	AY3	=IF([@[Planning Date]]="N/A","", IF(ISFORMULA([@[Planning Time]]),"", CONCATENATE(TEXT([@[Planning Date]],"dd/mm/yy")," ",TEXT([@[Planning Time]],"hh:mm am/pm"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AY3:AY9	Expression	=OR(AND(ABS(($U3+$V3)-($U2+$V2))*1440<29,$AX3="No",COUNTIF($AQ3,"Planning Time Approved*")=0),AND(ABS(($U4+$V4)-($U3+$V3))*1440<29,$AX4="No",COUNTIF($AQ4,"Planning Time Approved*")=0))	text	NO

Thank you so much! That seemed to have done the trick. I'll test it out for a few months and see how it fairs.

Thanks again!

 

[Peter_SSs]

You're welcome. Fingers crossed for the future. Thanks for the follow-up.