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]

can you provide some sample data with dates and expected outcomes for me to compare before submitting new formula?
suggest using xl2bb from link below

I'll work on it.

If it helps, this is the formula used previously for E15:AK15.... then I added AL15:AT15 which had the different variable for number of days. You may be able to understand better with the dates on the screen grab.

=IF(A15="","",IF(MIN(E15:AK15)=0,"Incomplete",IF(MIN(E15:AK15)<TODAY()-365,"Expired",IF(MEDIAN(TODAY()-365,TODAY()-335,MIN(E15:AK15))=MIN(E15:AK15),"Expires Soon","Safety Qualified"))))

 

[Charger_R_T]

can you provide some sample data with dates and expected outcomes for me to compare before submitting new formula?
suggest using xl2bb from link below

Machine Safety Qualification Tracker.xlsx
	A	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
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	Machine Center Name	Machine Center Name	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	Other	Other	Other	Other	Palletizer	Centering Device	Stretch Wrapper	Baler-Hogger	Other	Other	Other	Other	Other	Other	Other	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	Pull Pak Annual Evaluation	Box Clamp Truck Annual Evaluation	Fork Lift Annual Evaluation	Stand Up Fork Lift Annual Evaluation	Other Lift 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
16	Jaschob	Scott	Expired	FLL				05/19/22								05/19/22																							02/04/21
17	Kraus	Steven	Expired	Parkinson			10/31/22																							08/29/22
18	Mueller	Paul	Expired	FLL				05/19/22								05/19/22																						04/07/22
19	Sarauer	Katrina	Expires Soon	Laude			10/13/22
20	Supernand	Braden	Expires Soon	Parkinson						10/13/22	04/17/23
21	Batterman-Woelfel	Shelly	Incomplete	Buchholz																																				7/7/2020	05/17/23	05/17/23						Fork/Clamp
22	Schaefer	Allan	Incomplete	Parkinson																																				7/23/2020	05/19/23							Fork
23	Abhold	David	Safety Qualified	Jaschob		05/01/23	11/08/22
24	Abresch	Fred	Safety Qualified	Halle																02/02/23																				2/17/2023	05/04/23	05/04/23						Fork/Clamp
25	Baehler	Jeffrey	Safety Qualified	Parkinson				06/20/23
MSQ Tracker

Cell Formulas
Range	Formula
C15,C19:C25,C17	C15	=IF(A15="","",IF(MIN(E15:AK15)=0,"Incomplete",IF(MIN(E15:AK15)<TODAY()-365,"Expired",IF(MEDIAN(TODAY()-365,TODAY()-335,MIN(E15:AK15))=MIN(E15:AK15),"Expires Soon","Safety Qualified"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AL15:AM118,AW15:AW118	Expression	=ISBLANK(AL15)	text	NO
AL15:AM118,AW15:AW118	Expression	=AL15>TODAY()-$A$3	text	NO
AL15:AM118,AW15:AW118	Expression	=AL15<TODAY()-$A$2	text	NO
AL15:AM118,AW15:AW118	Expression	=AND(AL15<=TODAY()-$A$3)	text	NO
P15:Z118,AO15:AU118,AY15:AY118	Expression	=ISBLANK(P15)	text	NO
C15:C118	Cell Value	contains "Expires Soon"	text	NO
C15:C118	Cell Value	contains "Expired"	text	NO
C15:C118	Cell Value	contains "Safety Qualified"	text	NO
E15:O118,AA15:AK118	Expression	=ISBLANK(E15)	text	NO
E15:AK118,AO15:AU118,AY15:AY118	Expression	=E15>TODAY()-'Safety Qualificaton Elements'!$B$3	text	NO
E15:AK118,AO15:AU118,AY15:AY118	Expression	=E15<TODAY()-'Safety Qualificaton Elements'!$B$2	text	NO
E15:AK118,AO15:AU118,AY15:AY118	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:AU25	Date	<='Safety Qualificaton Elements'!$B$4
AW15:AW25	Date	<='Safety Qualificaton Elements'!$B$4
AY15:AY25	Date	<='Safety Qualificaton Elements'!$B$4
AA15:AM25	Date	<='Safety Qualificaton Elements'!$B$4
E15:V25	Date	<='Safety Qualificaton Elements'!$B$4

Cells A15:AK15 has the formula as needed for that range, however, I'm looking to add AL:AY with the different number of days. I appreciate your help.

If A15 is blank, C15=blank
IF A15 has text, but no dates entered through E15:AM15, AO15:AP15, and AR15:AT15 "Incomplete"
If E15:AK15, AO15:AU15, and AY15 has a date over 365 days prior to today or If AL15:AM15 and AW15, 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:AK15, AO15:AU15, and AY15 has a date between 335-365 days prior to today or If AL15:AM15 and AW15 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, AO15:AU15, and AY15 has all dates within 335 days prior to today and IF AL15:AM15 and AW15 has all dates within 1065, "Safety Qualified"

Columns AN, AV, & AX will not be part of the formula.

 

[ExceLoki]

try the last column
---------------

Nested If with dates & multiple day variables.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O												AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK															AZ	BA	BB
1					CONVERTING MACHINES																						SHIPPING/Baler																									365	2019	2016
14	EE Last Name	EE First Name	Status	Supervisor
15
16	Jaschob	Scott	Expired	FLL				5/19/2022																																												expired	expired	expired
17	Kraus	Steven	Expired	Parkinson			10/31/2022																							8/29/2022																						expired	expired	expired
18	Mueller	Paul	Expired	FLL				5/19/2022																																												expired	expired	expired
19	Sarauer	Katrina	Expires Soon	Laude			10/13/2022																																													expires soon	expires soon	expires soon
20	Supernand	Braden	Expires Soon	Parkinson						10/13/2022	4/17/2023																																									expires soon	expires soon	expires soon
21	Batterman-Woelfel	Shelly	Incomplete	Buchholz																																																safety qualified	safety qualified	safety qualified
22	Schaefer	Allan	Incomplete	Parkinson																																																safety qualified	safety qualified	safety qualified
23	Abhold	David	Safety Qualified	Jaschob		5/1/2023	11/8/2022																																													safety qualified	safety qualified	safety qualified
24	Abresch	Fred	Safety Qualified	Halle																																																safety qualified	safety qualified	safety qualified
25	Baehler	Jeffrey	Safety Qualified	Parkinson				6/20/2023																																												safety qualified	safety qualified	safety qualified
Sheet1 (2)

Cell Formulas
Range	Formula
C15,C19:C25,C17	C15	=IF(A15="","",IF(MIN(E15:AK15)=0,"Incomplete",IF(MIN(E15:AK15)<TODAY()-365,"Expired",IF(MEDIAN(TODAY()-365,TODAY()-335,MIN(E15:AK15))=MIN(E15:AK15),"Expires Soon","Safety Qualified"))))
AZ15:AZ25	AZ15	=LET(aa,MIN(E15:AK15,AO15:AU15,AY15),bb,AGGREGATE(15,6,AL15:AM15),IF(A15="","",IF(CONCAT(E15:AM15, AO15:AP15, AR15:AT15)="","incomplete",IF(OR(TODAY()-aa>365,IFERROR(OR(TODAY()-bb>1095,TODAY()-AGGREGATE(15,6,AW15)>1095),FALSE)),"expired",IF(OR( AND(TODAY()-aa<365,TODAY()-aa>335),AND(IFERROR(OR(TODAY()-bb<1095,TODAY()-bb>1065),FALSE),OR(TODAY()-AW15<1095,TODAY()-AW15>1065))),"expires soon","safety qualified")))))
BA15:BA25	BA15	=IF(A15="","",IF(CONCAT(E15:AM15, AO15:AP15, AR15:AT15)="","incomplete", IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,IFERROR(OR(TODAY()-AGGREGATE(15,6,AL15:AM15)>1095,TODAY()-AGGREGATE(15,6,AW15)>1095),FALSE)),"expired", IF(OR( AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335), AND(IFERROR(OR(TODAY()-AGGREGATE(15,6,AL15:AM15)<1095,TODAY()-AGGREGATE(15,6,AL15:AM15)>1065),FALSE),OR(TODAY()-AW15<1095,TODAY()-AW15>1065))),"expires soon", "safety qualified"))))
BB15:BB25	BB15	=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"incomplete", IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,IFERROR(OR(TODAY()-AGGREGATE(15,6,AL15:AM15)>1095,TODAY()-AGGREGATE(15,6,AW15)>1095),FALSE)),"expired", IF(OR( AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335), AND(IFERROR(OR(TODAY()-AGGREGATE(15,6,AL15:AM15)<1095,TODAY()-AGGREGATE(15,6,AL15:AM15)>1065),FALSE),OR(TODAY()-AW15<1095,TODAY()-AW15>1065))),"expires soon", "safety qualified"))))

 

[Charger_R_T]

try the last column
---------------

Nested If with dates & multiple day variables.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O												AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK															AZ	BA	BB
1					CONVERTING MACHINES																						SHIPPING/Baler																									365	2019	2016
14	EE Last Name	EE First Name	Status	Supervisor
15
16	Jaschob	Scott	Expired	FLL				5/19/2022																																												expired	expired	expired
17	Kraus	Steven	Expired	Parkinson			10/31/2022																							8/29/2022																						expired	expired	expired
18	Mueller	Paul	Expired	FLL				5/19/2022																																												expired	expired	expired
19	Sarauer	Katrina	Expires Soon	Laude			10/13/2022																																													expires soon	expires soon	expires soon
20	Supernand	Braden	Expires Soon	Parkinson						10/13/2022	4/17/2023																																									expires soon	expires soon	expires soon
21	Batterman-Woelfel	Shelly	Incomplete	Buchholz																																																safety qualified	safety qualified	safety qualified
22	Schaefer	Allan	Incomplete	Parkinson																																																safety qualified	safety qualified	safety qualified
23	Abhold	David	Safety Qualified	Jaschob		5/1/2023	11/8/2022																																													safety qualified	safety qualified	safety qualified
24	Abresch	Fred	Safety Qualified	Halle																																																safety qualified	safety qualified	safety qualified
25	Baehler	Jeffrey	Safety Qualified	Parkinson				6/20/2023																																												safety qualified	safety qualified	safety qualified
Sheet1 (2)

Cell Formulas
Range	Formula
C15,C19:C25,C17	C15	=IF(A15="","",IF(MIN(E15:AK15)=0,"Incomplete",IF(MIN(E15:AK15)<TODAY()-365,"Expired",IF(MEDIAN(TODAY()-365,TODAY()-335,MIN(E15:AK15))=MIN(E15:AK15),"Expires Soon","Safety Qualified"))))
AZ15:AZ25	AZ15	=LET(aa,MIN(E15:AK15,AO15:AU15,AY15),bb,AGGREGATE(15,6,AL15:AM15),IF(A15="","",IF(CONCAT(E15:AM15, AO15:AP15, AR15:AT15)="","incomplete",IF(OR(TODAY()-aa>365,IFERROR(OR(TODAY()-bb>1095,TODAY()-AGGREGATE(15,6,AW15)>1095),FALSE)),"expired",IF(OR( AND(TODAY()-aa<365,TODAY()-aa>335),AND(IFERROR(OR(TODAY()-bb<1095,TODAY()-bb>1065),FALSE),OR(TODAY()-AW15<1095,TODAY()-AW15>1065))),"expires soon","safety qualified")))))
BA15:BA25	BA15	=IF(A15="","",IF(CONCAT(E15:AM15, AO15:AP15, AR15:AT15)="","incomplete", IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,IFERROR(OR(TODAY()-AGGREGATE(15,6,AL15:AM15)>1095,TODAY()-AGGREGATE(15,6,AW15)>1095),FALSE)),"expired", IF(OR( AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335), AND(IFERROR(OR(TODAY()-AGGREGATE(15,6,AL15:AM15)<1095,TODAY()-AGGREGATE(15,6,AL15:AM15)>1065),FALSE),OR(TODAY()-AW15<1095,TODAY()-AW15>1065))),"expires soon", "safety qualified"))))
BB15:BB25	BB15	=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"incomplete", IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,IFERROR(OR(TODAY()-AGGREGATE(15,6,AL15:AM15)>1095,TODAY()-AGGREGATE(15,6,AW15)>1095),FALSE)),"expired", IF(OR( AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335), AND(IFERROR(OR(TODAY()-AGGREGATE(15,6,AL15:AM15)<1095,TODAY()-AGGREGATE(15,6,AL15:AM15)>1065),FALSE),OR(TODAY()-AW15<1095,TODAY()-AW15>1065))),"expires soon", "safety qualified"))))

Thank you! Just taken a quick look at it, it looks like that is what I need!

 

[ExceLoki]

awesome, glad could help
consider marking it as a solution for others to find

 

[Charger_R_T]

awesome, glad could help
consider marking it as a solution for others to find

Machine Safety Qualification Tracker.xlsx
	A	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
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	Machine Center Name	Machine Center Name	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	Other	Other	Other	Other	Palletizer	Centering Device	Stretch Wrapper	Baler-Hogger	Other	Other	Other	Other	Other	Other	Other	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	Pull Pak Annual Evaluation	Box Clamp Truck Annual Evaluation	Fork Lift Annual Evaluation	Stand Up Fork Lift Annual Evaluation	Other Lift 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	Safety Qualified	Parkinson				06/20/23																															09/30/20
MSQ Tracker

Cell Formulas
Range	Formula
C15:C17	C15	=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AP15:AR15)=0,"Incomplete", IF(OR(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>365,IFERROR(OR(TODAY()-AGGREGATE(15,6,AL15:AM15)>1095,TODAY()-AGGREGATE(15,6,AW15)>1095),FALSE)),"Expired", IF(OR( AND(TODAY()-MIN(E15:AK15,AO15:AU15,AY15)<365,TODAY()-MIN(E15:AK15,AO15:AU15,AY15)>335), AND(IFERROR(OR(TODAY()-AGGREGATE(15,6,AL15:AM15)<1095,TODAY()-AGGREGATE(15,6,AL15:AM15)>1065),FALSE),OR(TODAY()-AW15<1095,TODAY()-AW15>1065))),"Expires Soon", "Safety Qualified"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AL15:AM655,AW15:AW655	Expression	=ISBLANK(AL15)	text	NO
AL15:AM655,AW15:AW655	Expression	=AL15>TODAY()-$A$3	text	NO
AL15:AM655,AW15:AW655	Expression	=AL15<TODAY()-$A$2	text	NO
AL15:AM655,AW15:AW655	Expression	=AND(AL15<=TODAY()-$A$3)	text	NO
P15:Z655,AO15:AU655,AY15:AY655	Expression	=ISBLANK(P15)	text	NO
C15:C655	Cell Value	contains "Expires Soon"	text	NO
C15:C655	Cell Value	contains "Expired"	text	NO
C15:C655	Cell Value	contains "Safety Qualified"	text	NO
E15:O655,AA15:AK655	Expression	=ISBLANK(E15)	text	NO
E15:AK655,AO15:AU655,AY15:AY655	Expression	=E15>TODAY()-'Safety Qualificaton Elements'!$B$3	text	NO
E15:AK655,AO15:AU655,AY15:AY655	Expression	=E15<TODAY()-'Safety Qualificaton Elements'!$B$2	text	NO
E15:AK655,AO15:AU655,AY15:AY655	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:AU17	Date	<='Safety Qualificaton Elements'!$B$4
AW15:AW17	Date	<='Safety Qualificaton Elements'!$B$4
AY15:AY17	Date	<='Safety Qualificaton Elements'!$B$4
E15:AM17	Date	<='Safety Qualificaton Elements'!$B$4

Still having an issue with AL,AM, & AW changing the status to "epired" or "expires soon". In cells AM16 and AM17 I put in dates that should be returning "expired" and "expires soon" in C16 and C17 respectively.

 

[Charger_R_T]

@ExceLoki do You think this is doable? Otherwise, I could insert another column to the right of column C for the 3-year requirements?

 

[ExceLoki]

sorry for slow reply, been tinkering with this in between projects at work. think i've almost got it though...

 

[Charger_R_T]

sorry for slow reply, been tinkering with this in between projects at work. think i've almost got it though...

No worries. Thanks for the update! I too have been tinkering with it, with no success.

 

[ExceLoki]

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"))))