Nested If with dates & multiple day variables

[Charger_R_T]

I'm looking to place this formula in the status colume. I'll use row A15 as my example.
If A15 is blank, C15=blank
IF A15 has text, but no dates entered through E15:AM15, AO:AP, and AR:AT "Incomplete"
If E15:AM15, AO:AP, and AR:AT has a date over 365 days prior to today or If AL15:AM15 and AR15:AS15, has a date over 1095 days prior to today, "Expired". Even if there are dates within this row that are current or expiring soon.
If E15:AM15, AO:AP, and AR:AT has a date between 335-365 days prior to today or If AL15:AM15 and AR15:AS15 has a date between 1065-1095 days prior to today, "Expires Soon". Even if there are dates within this row that are current.
IF E15:AM15, AO:AP, and AR:AT has all dates within 335 days prior to today and IF AL15:AM15 and AR15:AS15 has all dates within 1065, "Safety Qualified"

Row AN, AQ, AS will not be part of the formula.

 

[Charger_R_T]

try this

=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"incomplete",IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,OR(TODAY()-AGGREGATE(5,6,AL15:AM15)>1095,IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))>1095,FALSE))),"expired",IF(OR(AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335),OR(IFERROR(AND(TODAY()-AGGREGATE(5,6,AL15:AM15)<1095,TODAY()-AGGREGATE(5,6,AL15:AM15)>1065),FALSE),OR(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))<1095,FALSE),IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))>1065,FALSE)))),"expires soon","safety qualified"))))

When an employee meets the safety qualified criteria it is retuning expired. It appears to be for employees that do not have data in the 1095 day cells.

Machine Safety Qualification Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M			P	Q	R	S	T	U	V					AA	AB	AC	AD								AL	AM	AN	AO	AP						AV	AW	AX	AY
1					CONVERTING MACHINES											CORRUGATOR											SHIPPING/Baler											Powered Industrial Truck Training
2	1095				5376 Martin FFG	2402 United RDC	2423 Ward RDC	2432 Ward RDC	5104 Ward FFG	5164 Ward FFG	5014 LMC FFG	1502 2pc Stitcher	Transfer Carts			Wet End (RS 7-10 & Trolleys, SF, Splicers)	Wet End (RS 3-6 & Trolleys, SF, Splicers)	Wet End (RS 1&2 & Trolleys, Trible Stack, Glue Machine)	Dry End (DB thru Knife)	Dry End (Knife thru Stacker)	Corrugator Palletizer	Starch Room					Palletizer	Centering Device	Stretch Wrapper	Baler-Hogger								PIT Train the Trainer Certified (Expires every 3 years)	Supervisor Class Room Training (Expires every 3 years)	Operator Class Room Training, Test, & Practical Exercies (Does not expire as long as Annual Eval is completed every year)	Fork Lift Annual Evaluation	Roll Clamp Truck Annual Evaluation						Truck Type Certifications	MEWP Train the Trainer Certified (Expires every 3 years)	MEWP Class Room Training, Exam, & Practical Evaluation	MEWP Annual Evaluation
3	730	Qualified Machine Operators
4
5			CONVERTING
6
7
8	ONLY SAFETY QUALIFIED EMPLOYEES ARE ALLOWED TO WORK ON ASSIGNED MACHINES
9
10
11
12
13	Remove employees by deleting the row. Add new employees at the bottom of the list.
14	EE Last Name	EE First Name	Status	Supervisor
15	Abhold	David	expired	Jaschob		05/01/23	11/08/22																															######	10/01/20
16	Abresch	Fred	expired	Halle																######																			09/21/20	2/17/2023	######	######						Fork/Clamp
17	Baehler	Jeffrey	expires soon	Parkinson				######																															######
18	Baker	Andrew	expired	Jaschob				04/11/23		01/04/23	######
19	Batterman-Woelfel	Shelly	expired	Buchholz																																				7/7/2020	05/17/23	05/17/23						Fork/Clamp
20	Baumann	Brandon	expired	Parkinson	04/10/23				01/06/23
21	Baumgartner	Matt	expired	Parkinson							11/17/22
22	Behling	Thorsten	expired	Laude	######			######
23	Benson	Curtis	incomplete	Laude
24	Bienash	Jason	expired	Smet																							08/14/23	######	######	######										2/17/2023	05/16/23							Fork
25	Blau	Erik	expired	Smet	12/12/22						######																			12/12/22										12/15/2022	######							Fork
26	Boede	Mike	expired	York				05/18/23								05/18/23																								5/7/2021	05/17/23							Fork		5/19/2021	07/19/23
27	Brooks	John	expired	Jaschob							11/02/22
MSQ Tracker

Cell Formulas
Range	Formula
C15:C27	C15	=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"incomplete",IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,OR(TODAY()-AGGREGATE(5,6,AL15:AM15)>1095,IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))>1095,FALSE))),"expired",IF(OR(AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335),OR(IFERROR(AND(TODAY()-AGGREGATE(5,6,AL15:AM15)<1095,TODAY()-AGGREGATE(5,6,AL15:AM15)>1065),FALSE),OR(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))<1095,FALSE),IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))>1065,FALSE)))),"expires soon","safety qualified"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AL15:AM654,AW15:AW654	Expression	=ISBLANK(AL15)	text	NO
AL15:AM654,AW15:AW654	Expression	=AL15>TODAY()-$A$3	text	NO
AL15:AM654,AW15:AW654	Expression	=AL15<TODAY()-$A$2	text	NO
AL15:AM654,AW15:AW654	Expression	=AND(AL15<=TODAY()-$A$3)	text	NO
P15:Z654,AO15:AU654,AY15:AY654	Expression	=ISBLANK(P15)	text	NO
C15:C654	Cell Value	contains "Expires Soon"	text	NO
C15:C654	Cell Value	contains "Expired"	text	NO
C15:C654	Cell Value	contains "Safety Qualified"	text	NO
E15:O654,AA15:AK654	Expression	=ISBLANK(E15)	text	NO
E15:AK654,AO15:AU654,AY15:AY654	Expression	=E15>TODAY()-'Safety Qualificaton Elements'!$B$3	text	NO
E15:AK654,AO15:AU654,AY15:AY654	Expression	=E15<TODAY()-'Safety Qualificaton Elements'!$B$2	text	NO
E15:AK654,AO15:AU654,AY15:AY654	Expression	=AND(E15<=TODAY()-'Safety Qualificaton Elements'!$B$3,E15>=TODAY()-'Safety Qualificaton Elements'!$B$2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E15:AM27	Date	<='Safety Qualificaton Elements'!$B$4
AY15:AY27	Date	<='Safety Qualificaton Elements'!$B$4
AW15:AW27	Date	<='Safety Qualificaton Elements'!$B$4
AO15:AU27	Date	<='Safety Qualificaton Elements'!$B$4

 

[ExceLoki]

ok, found that issue...

=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"incomplete",IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,OR(IFERROR(TODAY()-IF(ISNUMBER(AL15:AM15),IF(AL15:AM15>0,VALUE(AL15:AM15)),"p")>1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1095,FALSE))),"expired",IF(OR(AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335),OR(IFERROR(AND(TODAY()-AGGREGATE(5,6,AL15:AM15)<1095,TODAY()-AGGREGATE(5,6,AL15:AM15)>1065),FALSE),OR(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1065,FALSE)))),"expires soon","safety qualified"))))

 

[Charger_R_T]

ok, found that issue...

=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"incomplete",IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,OR(IFERROR(TODAY()-IF(ISNUMBER(AL15:AM15),IF(AL15:AM15>0,VALUE(AL15:AM15)),"p")>1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1095,FALSE))),"expired",IF(OR(AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335),OR(IFERROR(AND(TODAY()-AGGREGATE(5,6,AL15:AM15)<1095,TODAY()-AGGREGATE(5,6,AL15:AM15)>1065),FALSE),OR(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1065,FALSE)))),"expires soon","safety qualified"))))

Now it is not returning expired when it should be for the 1095 days. Everything else looks like it is working.

Machine Safety Qualification Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M			P	Q	R	S	T	U	V					AA	AB	AC	AD								AL	AM	AN	AO	AP						AV	AW	AX	AY
1					CONVERTING MACHINES											CORRUGATOR											SHIPPING/Baler											Powered Industrial Truck Training
2	1095				5376 Martin FFG	2402 United RDC	2423 Ward RDC	2432 Ward RDC	5104 Ward FFG	5164 Ward FFG	5014 LMC FFG	1502 2pc Stitcher	Transfer Carts			Wet End (RS 7-10 & Trolleys, SF, Splicers)	Wet End (RS 3-6 & Trolleys, SF, Splicers)	Wet End (RS 1&2 & Trolleys, Trible Stack, Glue Machine)	Dry End (DB thru Knife)	Dry End (Knife thru Stacker)	Corrugator Palletizer	Starch Room					Palletizer	Centering Device	Stretch Wrapper	Baler-Hogger								PIT Train the Trainer Certified (Expires every 3 years)	Supervisor Class Room Training (Expires every 3 years)	Operator Class Room Training, Test, & Practical Exercies (Does not expire as long as Annual Eval is completed every year)	Fork Lift Annual Evaluation	Roll Clamp Truck Annual Evaluation						Truck Type Certifications	MEWP Train the Trainer Certified (Expires every 3 years)	MEWP Class Room Training, Exam, & Practical Evaluation	MEWP Annual Evaluation
3	730	Qualified Machine Operators
4
5			CONVERTING
6
7
8	ONLY SAFETY QUALIFIED EMPLOYEES ARE ALLOWED TO WORK ON ASSIGNED MACHINES
9
10
11
12
13	Remove employees by deleting the row. Add new employees at the bottom of the list.
14	EE Last Name	EE First Name	Status	Supervisor
15	Abhold	David	safety qualified	Jaschob		05/01/23	11/08/22																																09/23/21
16	Abresch	Fred	safety qualified	Halle																02/02/23																			09/21/20	2/17/2023	05/04/23	05/04/23						Fork/Clamp
17	Baehler	Jeffrey	expires soon	Parkinson				06/20/23																															09/30/20
18	Baker	Andrew	safety qualified	Jaschob				04/11/23		01/04/23	08/29/23
19	Batterman-Woelfel	Shelly	safety qualified	Buchholz																																				7/7/2020	05/17/23	05/17/23						Fork/Clamp
20	Baumann	Brandon	safety qualified	Parkinson	04/10/23				01/06/23
21	Baumgartner	Matt	safety qualified	Parkinson							11/17/22
22	Behling	Thorsten	safety qualified	Laude	04/06/23			03/06/23
23	Benson	Curtis	incomplete	Laude
24	Bienash	Jason	safety qualified	Smet																							08/14/23	02/24/23	02/24/23	08/29/23										2/17/2023	05/16/23							Fork
25	Blau	Erik	safety qualified	Smet	12/12/22						03/06/23																			12/12/22										12/15/2022	04/27/23							Fork
26	Boede	Mike	safety qualified	York				05/18/23								05/18/23																								5/7/2021	05/17/23							Fork		5/19/2021	07/19/23
27	Brooks	John	safety qualified	Jaschob							11/02/22
28	Bruni	Dan	expires soon	Smet																									10/26/22	10/26/22										3/13/2020	05/31/23							Fork
29	Brzezinski	David	expires soon	Jaschob		08/17/23				10/27/22
30	Buchholz	Aaron	safety qualified	FLL				05/18/23								05/18/23																							06/09/22		05/10/23	05/10/23						Fork/Clamp
31	Drew	Ben	safety qualified	Parkinson	04/10/23					06/13/23
32	Duncan	Nakita	expired	Smet				11/02/22																					09/18/23	09/18/23										9/7/2023	09/07/22							Fork
33	Flynn	David	safety qualified	Parkinson		12/15/22
34	Gedamke	Mark	safety qualified	York				05/18/23								05/18/23																								3/23/2019	05/19/23							Fork		11/6/2019	07/19/23
35	Gentz	Samuel	Safety Qualified	Buchholz												11/01/22	11/01/22	11/01/22	11/01/22	11/01/22	11/01/22	11/01/22
36	Gilgenbach	Rick	Safety Qualified	FLL				05/18/23								05/18/23
37	Grady	Barry	Expired	Smet																									10/26/22	08/29/23										1/31/2022	05/31/23	09/21/22						Fork/Clamp
38	Graf	Ashley	Safety Qualified	Smet			06/08/23																				07/25/23	07/25/23												11/16/2021	09/08/23							Fork
MSQ Tracker

Cell Formulas
Range	Formula
C15:C34	C15	=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"incomplete",IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,OR(IFERROR(TODAY()-IF(ISNUMBER(AL15:AM15),IF(AL15:AM15>0,VALUE(AL15:AM15)),"p")>1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1095,FALSE))),"expired",IF(OR(AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335),OR(IFERROR(AND(TODAY()-AGGREGATE(5,6,AL15:AM15)<1095,TODAY()-AGGREGATE(5,6,AL15:AM15)>1065),FALSE),OR(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1065,FALSE)))),"expires soon","safety qualified"))))
C35:C38	C35	=IF(A35="","",IF(MAX(E35:AM35, AO35:AP35, AP35:AR35)=0,"Incomplete", IF(OR(TODAY()-MIN(E35:AK35,AO35:AU35,AY35)>365,IFERROR(OR(TODAY()-AGGREGATE(15,6,AL35:AM35)>1095,TODAY()-AGGREGATE(15,6,AW35)>1095),FALSE)),"Expired", IF(OR( AND(TODAY()-MIN(E35:AK35,AO35:AU35,AY35)<365,TODAY()-MIN(E35:AK35,AO35:AU35,AY35)>335), AND(IFERROR(OR(TODAY()-AGGREGATE(15,6,AL35:AM35)<1095,TODAY()-AGGREGATE(15,6,AL35:AM35)>1065),FALSE),OR(TODAY()-AW35<1095,TODAY()-AW35>1065))),"Expires Soon", "Safety Qualified"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AL15:AM647,AW15:AW647	Expression	=ISBLANK(AL15)	text	NO
AL15:AM647,AW15:AW647	Expression	=AL15>TODAY()-$A$3	text	NO
AL15:AM647,AW15:AW647	Expression	=AL15<TODAY()-$A$2	text	NO
AL15:AM647,AW15:AW647	Expression	=AND(AL15<=TODAY()-$A$3)	text	NO
P15:Z647,AO15:AU647,AY15:AY647	Expression	=ISBLANK(P15)	text	NO
C15:C647	Cell Value	contains "Expires Soon"	text	NO
C15:C647	Cell Value	contains "Expired"	text	NO
C15:C647	Cell Value	contains "Safety Qualified"	text	NO
E15:O647,AA15:AK647	Expression	=ISBLANK(E15)	text	NO
E15:AK647,AO15:AU647,AY15:AY647	Expression	=E15>TODAY()-'Safety Qualificaton Elements'!$B$3	text	NO
E15:AK647,AO15:AU647,AY15:AY647	Expression	=E15<TODAY()-'Safety Qualificaton Elements'!$B$2	text	NO
E15:AK647,AO15:AU647,AY15:AY647	Expression	=AND(E15<=TODAY()-'Safety Qualificaton Elements'!$B$3,E15>=TODAY()-'Safety Qualificaton Elements'!$B$2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
AO15:AU38	Date	<='Safety Qualificaton Elements'!$B$4
AW15:AW38	Date	<='Safety Qualificaton Elements'!$B$4
AY15:AY38	Date	<='Safety Qualificaton Elements'!$B$4
E15:AM38	Date	<='Safety Qualificaton Elements'!$B$4

 

[ExceLoki]

=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"incomplete",
IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,
OR(
IFERROR(TODAY()-IF(ISNUMBER(AL15),IF(AL15>0,VALUE(AL15)),"p")>1095,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AM15),IF(AM15>0,VALUE(AM15)),"p")>1095,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1095,FALSE))),"expired",
IF(OR(
AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335),
OR(
AND(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AL15),IF(AL15>0,AL15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AL15),IF(AL15>0,VALUE(AL15)),"p")>1065,FALSE)),
AND(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AM15),IF(AM15>0,AM15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AM15),IF(AM15>0,VALUE(AM15)),"p")>1065,FALSE)),
AND(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1065,FALSE))
)),"expires soon","safety qualified"))))

 

[Charger_R_T]

=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"incomplete",
IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,
OR(
IFERROR(TODAY()-IF(ISNUMBER(AL15),IF(AL15>0,VALUE(AL15)),"p")>1095,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AM15),IF(AM15>0,VALUE(AM15)),"p")>1095,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1095,FALSE))),"expired",
IF(OR(
AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335),
OR(
AND(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AL15),IF(AL15>0,AL15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AL15),IF(AL15>0,VALUE(AL15)),"p")>1065,FALSE)),
AND(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AM15),IF(AM15>0,AM15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AM15),IF(AM15>0,VALUE(AM15)),"p")>1065,FALSE)),
AND(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1065,FALSE))
)),"expires soon","safety qualified"))))

Looks like it is not accounting for 1095-1065 expires soon. See cell AM17

Machine Safety Qualification Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M			P	Q	R	S	T	U	V					AA	AB	AC	AD								AL	AM	AN	AO	AP						AV	AW	AX	AY
1					CONVERTING MACHINES											CORRUGATOR											SHIPPING/Baler											Powered Industrial Truck Training
2	1095				5376 Martin FFG	2402 United RDC	2423 Ward RDC	2432 Ward RDC	5104 Ward FFG	5164 Ward FFG	5014 LMC FFG	1502 2pc Stitcher	Transfer Carts			Wet End (RS 7-10 & Trolleys, SF, Splicers)	Wet End (RS 3-6 & Trolleys, SF, Splicers)	Wet End (RS 1&2 & Trolleys, Trible Stack, Glue Machine)	Dry End (DB thru Knife)	Dry End (Knife thru Stacker)	Corrugator Palletizer	Starch Room					Palletizer	Centering Device	Stretch Wrapper	Baler-Hogger								PIT Train the Trainer Certified (Expires every 3 years)	Supervisor Class Room Training (Expires every 3 years)	Operator Class Room Training, Test, & Practical Exercies (Does not expire as long as Annual Eval is completed every year)	Fork Lift Annual Evaluation	Roll Clamp Truck Annual Evaluation						Truck Type Certifications	MEWP Train the Trainer Certified (Expires every 3 years)	MEWP Class Room Training, Exam, & Practical Evaluation	MEWP Annual Evaluation
3	730	Qualified Machine Operators
4
5			CONVERTING
6
7
8	ONLY SAFETY QUALIFIED EMPLOYEES ARE ALLOWED TO WORK ON ASSIGNED MACHINES
9
10
11
12
13	Remove employees by deleting the row. Add new employees at the bottom of the list.
14	EE Last Name	EE First Name	Status	Supervisor
15	Abhold	David	expired	Jaschob		05/01/23	11/08/22																															09/23/20	09/23/21
16	Abresch	Fred	expired	Halle																02/02/23																			09/21/20	2/17/2023	05/04/23	05/04/23						Fork/Clamp
17	Baehler	Jeffrey	safety qualified	Parkinson				06/20/23																															10/04/20
18	Baker	Andrew	safety qualified	Jaschob				04/11/23		01/04/23	08/29/23
19	Batterman-Woelfel	Shelly	safety qualified	Buchholz																																				7/7/2020	05/17/23	05/17/23						Fork/Clamp
20	Baumann	Brandon	safety qualified	Parkinson	04/10/23				01/06/23
21	Baumgartner	Matt	safety qualified	Parkinson							11/17/22
22	Behling	Thorsten	safety qualified	Laude	04/06/23			03/06/23
23	Benson	Curtis	incomplete	Laude
24	Bienash	Jason	safety qualified	Smet																							08/14/23	02/24/23	02/24/23	08/29/23										2/17/2023	05/16/23							Fork
25	Blau	Erik	safety qualified	Smet	12/12/22						03/06/23																			12/12/22										12/15/2022	04/27/23							Fork
26	Boede	Mike	safety qualified	York				05/18/23								05/18/23																								5/7/2021	05/17/23							Fork		5/19/2021	07/19/23
27	Brooks	John	safety qualified	Jaschob							11/02/22
28	Bruni	Dan	expires soon	Smet																									10/26/22	10/26/22										3/13/2020	05/31/23							Fork
29	Brzezinski	David	expires soon	Jaschob		08/17/23				10/27/22
30	Buchholz	Aaron	safety qualified	FLL				05/18/23								05/18/23																							06/09/22		05/10/23	05/10/23						Fork/Clamp
31	Buchholz	Jacque	Safety Qualified	Parkinson									10/31/22
MSQ Tracker

Cell Formulas
Range	Formula
C15:C30	C15	=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"incomplete", IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365, OR( IFERROR(TODAY()-IF(ISNUMBER(AL15),IF(AL15>0,VALUE(AL15)),"p")>1095,FALSE), IFERROR(TODAY()-IF(ISNUMBER(AM15),IF(AM15>0,VALUE(AM15)),"p")>1095,FALSE), IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1095,FALSE))),"expired", IF(OR( AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335), OR( AND(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AL15),IF(AL15>0,AL15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AL15),IF(AL15>0,VALUE(AL15)),"p")>1065,FALSE)), AND(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AM15),IF(AM15>0,AM15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AM15),IF(AM15>0,VALUE(AM15)),"p")>1065,FALSE)), AND(IFERROR(TODAY()-AGGREGATE(5,6,MIN(IF(ISNUMBER(AW15),IF(AW15>0,AW15),"p")))<1095,FALSE),IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1065,FALSE)) )),"expires soon","safety qualified"))))
C31	C31	=IF(A31="","",IF(MAX(E31:AM31, AO31:AP31, AP31:AR31)=0,"Incomplete", IF(OR(TODAY()-MIN(E31:AK31,AO31:AU31,AY31)>365,IFERROR(OR(TODAY()-AGGREGATE(15,6,AL31:AM31)>1095,TODAY()-AGGREGATE(15,6,AW31)>1095),FALSE)),"Expired", IF(OR( AND(TODAY()-MIN(E31:AK31,AO31:AU31,AY31)<365,TODAY()-MIN(E31:AK31,AO31:AU31,AY31)>335), AND(IFERROR(OR(TODAY()-AGGREGATE(15,6,AL31:AM31)<1095,TODAY()-AGGREGATE(15,6,AL31:AM31)>1065),FALSE),OR(TODAY()-AW31<1095,TODAY()-AW31>1065))),"Expires Soon", "Safety Qualified"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AL15:AM654,AW15:AW654	Expression	=ISBLANK(AL15)	text	NO
AL15:AM654,AW15:AW654	Expression	=AL15>TODAY()-$A$3	text	NO
AL15:AM654,AW15:AW654	Expression	=AL15<TODAY()-$A$2	text	NO
AL15:AM654,AW15:AW654	Expression	=AND(AL15<=TODAY()-$A$3)	text	NO
P15:Z654,AO15:AU654,AY15:AY654	Expression	=ISBLANK(P15)	text	NO
C15:C654	Cell Value	contains "Expires Soon"	text	NO
C15:C654	Cell Value	contains "Expired"	text	NO
C15:C654	Cell Value	contains "Safety Qualified"	text	NO
E15:O654,AA15:AK654	Expression	=ISBLANK(E15)	text	NO
E15:AK654,AO15:AU654,AY15:AY654	Expression	=E15>TODAY()-'Safety Qualificaton Elements'!$B$3	text	NO
E15:AK654,AO15:AU654,AY15:AY654	Expression	=E15<TODAY()-'Safety Qualificaton Elements'!$B$2	text	NO
E15:AK654,AO15:AU654,AY15:AY654	Expression	=AND(E15<=TODAY()-'Safety Qualificaton Elements'!$B$3,E15>=TODAY()-'Safety Qualificaton Elements'!$B$2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E15:AM31	Date	<='Safety Qualificaton Elements'!$B$4
AY15:AY31	Date	<='Safety Qualificaton Elements'!$B$4
AW15:AW31	Date	<='Safety Qualificaton Elements'!$B$4
AO15:AU31	Date	<='Safety Qualificaton Elements'!$B$4

 

[ExceLoki]

ok, i changed up the logic on it some. hoping this should do it.
if not, could you please give me the expected results along with the dates so i can test more before posting here?

=IF(A15="","",IF(MAX(E15:AM15,AO15:AP15,AP15:AR15)=0,"incomplete",
IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,
OR(
IFERROR(TODAY()-IF(ISNUMBER(AL15),IF(AL15>0,VALUE(AL15)),"p")>1095,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AM15),IF(AM15>0,VALUE(AM15)),"p")>1095,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1095,FALSE))),"expired",
IF(OR(
TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335,
OR(
IFERROR(TODAY()-IF(ISNUMBER(AL15),IF(AL15>0,VALUE(AL15)),"p")>1065,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AM15),IF(AM15>0,VALUE(AM15)),"p")>1065,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1065,FALSE)
)),"expires soon","safety qualified"))))

 

[Charger_R_T]

ok, i changed up the logic on it some. hoping this should do it.
if not, could you please give me the expected results along with the dates so i can test more before posting here?

=IF(A15="","",IF(MAX(E15:AM15,AO15:AP15,AP15:AR15)=0,"incomplete",
IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,
OR(
IFERROR(TODAY()-IF(ISNUMBER(AL15),IF(AL15>0,VALUE(AL15)),"p")>1095,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AM15),IF(AM15>0,VALUE(AM15)),"p")>1095,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1095,FALSE))),"expired",
IF(OR(
TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335,
OR(
IFERROR(TODAY()-IF(ISNUMBER(AL15),IF(AL15>0,VALUE(AL15)),"p")>1065,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AM15),IF(AM15>0,VALUE(AM15)),"p")>1065,FALSE),
IFERROR(TODAY()-IF(ISNUMBER(AW15),IF(AW15>0,VALUE(AW15)),"p")>1065,FALSE)
)),"expires soon","safety qualified"))))

Just looking at it quick i think that is it!! I appreciate the work you put into this! thank you!

 

[ExceLoki]

awesome, glad was able to get it to work for you.
happy to help