If, Then, But also... formula :)

[excelidiotSC]

Hi. I have a simple spreadsheet for scheduling periodic PM work for 95 injection molding machines at my plant.

Brief setup:
We have two levels of PM work, based on machine cycles:
· 1,000,000 Cycles = Lvl 10 PM
· 2,000,000 Cycles = Lvl 20 PM
If we perform a 20 on a machine, then the next PM will be a 10 and vice versa.
However, in case a machine doesn't run very often, we have to perform a Lvl 20 PM at least once a year.

SO... in the below spreadsheet example, how do I write a logic-based formula that will read the value in Column H (either 10 or 20) and generate the appropriate value in Column E, given the Date entered in Column I?

I could see the following logic statements being true and possibly helpful:
· If Hx>10, then Ex=10
· But, if Ix< Today minus 365days, then Ex=20

Unfortunately, IT won't allow me to install XL2BB.
And, yes, I know we're rather behind on PM work... Haha

--Steve

 

[Z51]

Does this work?

Book1.xlsx
	A	B	C		E	F		H	I	J
1	CELL	MACH	AUTO		THIS PM	PROJ DATE		LAST PM	LAST DATE	Reason
2	C1	AF	N/A		10	5/25/2023		20	3/18/2022	Last PM was a 20 and Last Date < 1 year
3	C1	AG	N/A		20	5/24/2023		20	2/25/2021	Last PM was a 20, but Last Date > 1 year
4	C1	AH	N/A		20	1/16/2023		20	8/4/2021	Last PM was a 20, but Last Date > 1 year
5	C1	AK	N/A		10	4/29/2023		20	5/18/2022	Last PM was a 20 and Last Date < 1 year
6	C1	GA	N/A		20	4/13/2023		20	11/24/2020	Last PM was a 20, but Last Date > 1 year
7	C1	GD	N/A		20	4/14/2023		20	8/16/2021	Last PM was a 20, but Last Date > 1 year
8	C2	AE	N/A		20	4/13/2023		20	10/1/2021	Last PM was a 20, but Last Date > 1 year
9	C2	CU	N/A		20	11/30/2023		20	11/18/2020	Last PM was a 20, but Last Date > 1 year
10	C2	HB	N/A		20	12/31/2022		20	11/12/2021	Last PM was a 20, but Last Date > 1 year
11	C2	HD	ROBAG 1		10	9/12/2023		20	11/15/2022	Last PM was a 20 and Last Date < 1 year
12	C2	MF	N/A		10	9/19/2022		20	3/1/2022	Last PM was a 20 and Last Date < 1 year
13	C3	BT	SP-BT		20	7/24/2022		10	11/24/2021	Last PM was a 10
14	C3	BY	SP-BY		20	9/5/2022		20	10/27/2021	Last PM was a 20, but Last Date > 1 year
15	C3	IF	RKR 37		20	7/8/2022		10	3/4/2022	Last PM was a 10
16	C3	IG	RKR 35		20	3/24/2022		20	10/10/2021	Last PM was a 20, but Last Date > 1 year
17	C3	MJ	SP-MJ		20	1/10/2023		20	11/5/2021	Last PM was a 20, but Last Date > 1 year
18	C3	MK	N/A		20	10/29/2022		20	1/12/2021	Last PM was a 20, but Last Date > 1 year
Sheet1

Cell Formulas
Range	Formula
E2:E18	E2	=IF(OR(I2<(TODAY()-365),H2=10),20,10)

 

[excelidiotSC]

Thanks for the quick reply! That clearly seems to work in your example, but only generates 20s in my spreadsheet.. I'll play around with it and see if I can figure out why.

--Steve

 

[excelidiotSC]

Z51 - works perfectly! Thank you.

True to my username, I had reorganized my spreadsheet prior to using your formula, so the columns were no longer the same. Edited columns in formula and works now. Thanks so much!

--Steve

 

[Z51]

You're welcome, glad you got it sorted out!