IF, OR, AND formula required

[aryanaveen]

Hi All,

Can you please help me with a formula to sort out the below scenario

I am looking for a formula in Column E which will help me to check if the product is "On track" or "Off track" based on below conditions and max days

Timelines for the stages

Type B Product

Type A Product

If the product type is B then below rule
if the order is more than 30 days from current date and the stage is at approval then "off track", if not "On Track"
if the order is more than 45 days from current date and the stage is at Prepare then "off track", if not "On Track"
if the order is more than 60 days from current date and the stage is at QC then "off track", if not "On Track"
if the order is more than 75 days from current date and the stage is at Final QC then "off track", if not "On Track"
if the order is more than 85 days from current date and the stage is at Packing then "off track", if not "On Track"
if the order is more than 90 days from current date and the stage is at Delivered then "off track", if not "On Track"

If the product type is A then below rule
if the order is more than 90 days from current date and the stage is at approval then "off track", if not "On Track"
if the order is more than 150 days from current date and the stage is at Prepare then "off track", if not "On Track"
if the order is more than 180 days from current date and the stage is at QC then "off track", if not "On Track"
if the order is more than 240 days from current date and the stage is at Final QC then "off track", if not "On Track"
if the order is more than 330 days from current date and the stage is at Packing then "off track", if not "On Track"
if the order is more than 360 days from current date and the stage is at Delivered then "off track", if not "On Track"

 

[JamesCanale]

MrExcelPlayground17.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Date	Type	Stage	Status		Step	Type A	Type B
2	Soap	12/31/2022	A	QC	On Track		Approval	90	30
3	Face wash	12/31/2022	B	Final QC	off track		Prepare	150	45
4	Shampoo	3/31/2022	A	Approval	off track		QC	180	60
5	Hand wash	10/18/2022	B	Prepare	off track		Final QC	240	75
6	Sanitizer	12/31/2022	B	QC	off track		Packing	330	85
7	Deo	10/31/2022	B	Final QC	off track		Delivered	360	90
8	Perfume	8/31/2022	A	Packing	On Track
9	Napkins	3/31/2023	A	Delivered	On Track
Sheet16

Cell Formulas
Range	Formula
E2:E9	E2	=IF(TODAY()-B2>VLOOKUP(D2,$G$2:$I$7,IF(C2="A",2,3),FALSE),"off track","On Track")