conditional formating formula question

[biglb79]

is there a formula I could use that would highlight cells AB3 and and AC3 in green if all cells G3:AA3 have been initialed and dated? I don't know the exact length for both. someone could use BB 10/28 or BBB 10/28/20

2nd part of formula would be to have them highlighted in red if there isn't an initial and date? currently names are listed and everyone is replacing their name with initials and date once completed so it can't just be any type of fill in the cell. hopefully my question makes sense. thanks in advance!

 

[biglb79]

Very messy , must be a better way

=SUMPRODUCT(LEN(G3:AA3)-LEN(SUBSTITUTE(G3:AA3," ","")))=21
as a formula in the conditional format for green

=SUMPRODUCT(LEN(G3:AA3)-LEN(SUBSTITUTE(G3:AA3," ","")))<>21
for RED

OR
=COUNTIF($G$3:$AA$3,"*/*")=21

thanks so much! I got the red working but not the green yet and I populated cells G3:AA3 with BB 10/28, but you did give me enough to work with and figure out on my own the rest of the way

 

[etaf]

countif() , using the / , may be better, as should only count the cells once
if everyone uses the / for a date entry

looking into last character as a number

 

[biglb79]

for ease I have just used 5 columns rather than 21

Book1
	F	G	H	I	J	K	L	M	N
1		G3 to AA3						AB3	AC3
2		fred	****	harry t	bb 10/28	cc 10/28
3		bb 10/28	cc 10/28	bb 10/29	cc 10/29	bb 10/30
4
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:M3	Expression	=COUNTIF(G3:AA3,"*/*")=5	text	NO
M2:M3	Expression	=COUNTIF($G3:$AA3,"*/*")<>5	text	NO

that's perfect! thank you so much!

 

[etaf]

Book1
	F	G	H	I	J	K	L	M	N	O
1		G3 to AA3						AB3	AC3
2		fred	****	harry t	bb 10/28/	cc 10/28				FALSE
3		bb 10/28	cc 10/28	bb 10/29	cc 10/29	bb 10/30				TRUE
4
Sheet2

Cell Formulas
Range	Formula
O2:O3	O2	=COUNTIF($G2:$K2,"*/*")=5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:M3	Expression	=COUNTIF($G2:$K2,"*/*")=5	text	YES
M2:M3	Expression	=COUNTIF($G2:$K2,"*/*")<>5	text	YES