How to return a true/false based on an entire row of cells containing dates, looking for any date that is expired

[christinacoleman79]

I am working on a training matrix to track an entire crew's marine certifications. Is it possible for me to have a column showing if they are in compliance or not according to all of their certification dates listed in a row of cells?

 

[christinacoleman79]

can you explain, not sure what you mean here exactly




more than happy to explain as best i can - or point to a website , which can explain better
mrexcel also has a youtube channel

The columns that show all the different certifications, I need to know how many expired or missing I have in each column. I have to be able to show information by person or by certification.

 

[etaf]

oh, OK
thats a new request rather than just a TRUE / FALSE

I have to be able to show information by person or by certification.

which is it ?
or both?
where will this be provided ?
as a person could be missing 10's of certs

as you have 2021 version
should have textjoin()
if you put
=textjoin(
in the formula bar - does it give an explanation
also
=filter()

i'm thinking of maybe joining the names to appear - rather than TRUE / FALSE

=TEXTJOIN(" / ",,FILTER(H1:BA1,(H7:BA7="")+(H7:BA7<TODAY())))
BUT that will produce a huge text in a cell if multiple certs are missing

row7 for example
Maybe the delimter needs to be different - something like "--/--" maybe

Coleman_Practice Matrix-ETAF.xlsx
	F	G	H	I	J	K
1			Bloodborne Pathogens	Hearing Conservation	Respirator Trainning (All Employees) (Fit Test as needed)	Drug & Alcohol Aware ADAPT (CBT)
2
3
4			RBT-JAN	RBT-FEB	RBT-FEB	RBT-MAR
5	Some Items are Position Specific		Annual	Annual	Annual	Annual
6	Emp #	In Compliance	Bloodborne Pathogens	Hearing Conservation	Respirator Trainning (All Employees) (Fit Test as needed)	Drug & Alcohol Aware ADAPT (CBT)
7	7623	Bloodborne Pathogens / Hearing Conservation / Respirator Trainning (All Employees) (Fit Test as needed) / Crew Security Awareness / SEMS Awareness	31-Dec-23			5-Mar-24
8	14605	FALSE	30-Dec-24	3-Feb-25	3-Feb-25	19-Mar-24
9	13311	FALSE	30-Dec-24	3-Feb-25	3-Feb-25	5-Mar-24
10	12132	FALSE	30-Dec-24	3-Feb-25	3-Feb-25	5-Mar-24
NEW Matrix

Cell Formulas
Range	Formula
H6:K6	H6	=H1
G7	G7	=TEXTJOIN(" / ",,FILTER(H1:BA1,(H7:BA7="")+(H7:BA7<TODAY())))
G8:G10	G8	=OR( COUNTIF(H8:BA8,"")>0, COUNTIFS(H8:BA8,"<"&TODAY(),H$5:BA$5,"<>"&"once")>0 )

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AP10:AQ10,H10,AE10:AN10,S10:T10,AC10,M10:O10,V10:AA10,K10	Cell Value	between NOW() and NOW()+30	text	NO
AP10:AQ10,H10,AE10:AN10,S10:T10,AC10,M10:O10,V10:AA10,K10	Cell Value	<NOW()	text	NO
H7:H9	Cell Value	between NOW() and NOW()+30	text	NO
H7:H9	Cell Value	<NOW()	text	NO
AY8,BA8,AY11:AY13,BA11:BA14,AY16,BA16,O33,AW8:AW9,I7:Q7,AE9:AS9,AD9:AD10,K11:K19,I8:K8,P8:Q19,S11:T19,M8:O9,M11:O19,L8:L19,M28:O32,M33,M34:O36,S7:T9,R7:R19,V7:AS8,V11:AS19,V9:AC9,U7:U19,AX17:BA20,K9,AU24:AZ24,H28:L36,P28:AS36,H11:H23,K20:AS23,AY21:AY23	Cell Value	between NOW() and NOW()+30	text	NO
AY8,BA8,AY11:AY13,BA11:BA14,AY16,BA16,O33,AW8:AW9,I7:Q7,AE9:AS9,AD9:AD10,K11:K19,I8:K8,P8:Q19,S11:T19,M8:O9,M11:O19,L8:L19,M28:O32,M33,M34:O36,S7:T9,R7:R19,V7:AS8,V11:AS19,V9:AC9,U7:U19,AX17:BA20,K9,AU24:AZ24,H28:L36,P28:AS36,H11:H23,K20:AS23,AY21:AY23	Cell Value	<NOW()	text	NO

 

[christinacoleman79]

The columns containing all the dates of the certifications, I need to know how many I have per column.

 

[etaf]

where would you show that info ?

 

[christinacoleman79]

where would you show that info ?

I was going to run the totals per column along the very bottom row, then pull the numbers from the columns with blue headings into a small table at the top for them to reference quickly.

 

[etaf]

ok, then just use a count for each column
=IF(H5="once",COUNTIF(H7:H40,""),COUNTIF(H7:H40,"")+COUNTIFS(H7:H40,"<"&TODAY()))

 

[smozgur]

Thank you so much for your assistance.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.