I need help. This is beyond my skill set but I'm willing to learn.

[colemanj26]

I am working on a new Task sheet and am unsure how to write these formulas. I need to make a Mold PM (preventative maintenance) task sheet that is based on a yearly calendar. The sheet is based on a set number of days. So, say the mold has to be PM every 30 uses after 30 uses I need the numbers to go into the negative reflecting that we missed the expected day when it has been done, I need to be able reset it back to the 30 after the PM has to been completed. I also need it to have a subtraction formula in the days of the month so that it can be subtracted from the 30 uses until the task needs to be completed again. I know this is confusing, but I have an understanding of excel but this is beyond my skill set.

1) I need the days to subtract from the total uses everyday it is used (ex. I used the mold Mon, Thursday and Sunday, so it would subtract 3 uses from the total uses of 30 leaving it at 27. Once it goes to 0 uses, I need it to go into the negative (I assume this will be conditional formatting to make it red or something like that)

2) I need the days in the month columns to automatically subtract from overall uses once I input that it was used on that day. (ex. I used it 17 times in January which means I have 13 (Based on 30-use cycle) more uses of that mold before I have to pull it out and clean/repair it)

3) Once the PM is completed, I need to be able to reset the counter, but I need to be able to reset the counter on the day it was put back into production. (If it is taken out of production on Feb 16 and was returned to the shop on Feb 20. I need the file to reflect the new 30 uses starts on Feb 20)

Please help I have gotten some aspects to work but I can't link it together properly. I have tried -SUMIF, DATEDIF, TIME () conditional formatting =MAX/MIN. I have tooled around with every formula I can think of and can't get it to work. I know you may ask me to share the file but I cannot because it is a work-related project, and no one know how to help me. I just need some formula ideas that I can try to string together.

Thank you

 

[etaf]

how is the data laid out ? - see below , provide a sample using XL2BB or share
is it just 1 mould

do you just list the dates it was used and then a day its returned - so just a list

i may have really overcomplicated this
=IF(B2="returned",30,30-COUNTIF(INDIRECT(ADDRESS(MATCH(MAXIFS($A$2:A2,$C$2:C2,30),$A$2:A2,0)+1,2)&":"&ADDRESS(ROW(),2,4)),"used"))

Now you can use the 0 to flag it needs a PM .... using an IF statement - you could also add a colour amber when nearing to zero - say 5 uses left before PM needed

so a conditional format less than 0 for red

if not , then we need to see the layout you want or have so far

As i say - i may have overcomplicated this - and with helper columns may be a simplier formula - i'll have a look and see

Book1
	A	B	C
1	Dates	Status	PM Required
2	1/1/23	returned	30
3	1/2/23	Used	29
4	1/3/23	Used	28
5	1/4/23	Used	27
6	1/5/23	Used	26
7	1/6/23	Used	25
8	1/7/23	Used	24
9	1/8/23	Used	23
10	1/9/23	Used	22
11	1/10/23	Used	21
12	1/11/23	Used	20
13	1/12/23	returned	30
14	1/13/23	Used	29
15	1/14/23	Used	28
16	1/15/23	Used	27
17	1/16/23	Used	26
18	1/17/23	Used	25
19	1/18/23	Used	24
20	1/19/23	Used	23
21	1/20/23	Used	22
22	1/21/23	Used	21
23	2/1/23	Returned	30
24	1/23/23	Used	29
25	1/24/23	Used	28
26	1/25/23	Used	27
27	1/26/23	Used	26
28	1/27/23	Used	25
29	1/28/23	Used	24
30	1/29/23	Used	23
31	1/30/23	Used	22
32	1/31/23	Used	21
33	2/1/23	Used	20
34	2/2/23	Used	19
35	2/3/23	Used	18
36	2/4/23	Used	17
37	2/5/23	Used	16
38	2/6/23	Used	15
39	2/7/23	Used	14
40	2/8/23	Used	13
41	2/9/23	Used	12
42	2/10/23	Used	11
43	2/11/23	Used	10
44	2/12/23	Used	9
45	2/13/23	Used	8
46	2/14/23	Used	7
47	2/15/23	Used	6
48	2/16/23	Used	5
49	2/17/23	Used	4
50	2/18/23	Used	3
51	2/19/23	Used	2
52	2/20/23	Used	1
53	2/21/23	Used	0
54	2/22/23	Used	-1
55	2/23/23	Used	-2
56	2/24/23	Used	-3
57	2/25/23	Used	-4
58	2/26/23	Used	-5
59	2/27/23	Used	-6
60	2/28/23	Used	-7
61	3/1/23	Used	-8
62	3/2/23	Used	-9
63	3/3/23	Used	-10
64	3/4/23	Used	-11
65	3/5/23	Used	-12
66	3/6/23	Used	-13
67	3/7/23	Used	-14
Sheet1

Cell Formulas
Range	Formula
C2:C67	C2	=IF(B2="returned",30,30-COUNTIF(INDIRECT(ADDRESS(MATCH(MAXIFS($A$2:A2,$C$2:C2,30),$A$2:A2,0)+1,2)&":"&ADDRESS(ROW(),2,4)),"used"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:C67	Expression	=$C1<0	text	NO

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[etaf]

OK, howabout a much simpler formula
=IF(B2="returned",30,D1-1)

As i say , I really overcomplicated that .......

starts with the item returned in row 2

and added an IF
=IF(C2<0,"Pm Overdue",IF(C2<5,C2&" days to PM",""))

plus amber conditional formatting

Book1
	A	B	C	D
1	Dates	Status	Days to PM	Status
2	1/1/23	returned	30
3	1/2/23	Used	29
4	1/3/23	Used	28
5	1/4/23	Used	27
6	1/5/23	Used	26
7	1/6/23	Used	25
8	1/7/23	Used	24
9	1/8/23	Used	23
10	1/9/23	Used	22
11	1/10/23	Used	21
12	1/11/23	Used	20
13	1/12/23	returned	30
14	1/13/23	Used	29
15	1/14/23	Used	28
16	1/15/23	Used	27
17	1/16/23	Used	26
18	1/17/23	Used	25
19	1/18/23	Used	24
20	1/19/23	Used	23
21	1/20/23	Used	22
22	1/21/23	Used	21
23	2/1/23	Returned	30
24	1/23/23	Used	29
25	1/24/23	Used	28
26	1/25/23	Used	27
27	1/26/23	Used	26
28	1/27/23	Used	25
29	1/28/23	Used	24
30	1/29/23	Used	23
31	1/30/23	Used	22
32	1/31/23	Used	21
33	2/1/23	Used	20
34	2/2/23	Used	19
35	2/3/23	Used	18
36	2/4/23	Used	17
37	2/5/23	Used	16
38	2/6/23	Used	15
39	2/7/23	Used	14
40	2/8/23	Used	13
41	2/9/23	Used	12
42	2/10/23	Used	11
43	2/11/23	Used	10
44	2/12/23	Used	9
45	2/13/23	Used	8
46	2/14/23	Used	7
47	2/15/23	Used	6
48	2/16/23	Used	5
49	2/17/23	Used	4	4 days to PM
50	2/18/23	Used	3	3 days to PM
51	2/19/23	Used	2	2 days to PM
52	2/20/23	Used	1	1 days to PM
53	2/21/23	Used	0	0 days to PM
54	2/22/23	Used	-1	Pm Overdue
55	2/23/23	Used	-2	Pm Overdue
56	2/24/23	Used	-3	Pm Overdue
57	2/25/23	Used	-4	Pm Overdue
58	2/26/23	Used	-5	Pm Overdue
59	2/27/23	Used	-6	Pm Overdue
60	2/28/23	Used	-7	Pm Overdue
61	3/1/23	Used	-8	Pm Overdue
62	3/2/23	Used	-9	Pm Overdue
63	3/3/23	Used	-10	Pm Overdue
64	3/4/23	Used	-11	Pm Overdue
65	3/5/23	Used	-12	Pm Overdue
66	3/6/23	Used	-13	Pm Overdue
67	3/7/23	Used	-14	Pm Overdue
Sheet1

Cell Formulas
Range	Formula
C2:C67	C2	=IF(B2="returned",30,C1-1)
D2:D67	D2	=IF(C2<0,"Pm Overdue",IF(C2<5,C2&" days to PM",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:D100	Expression	=$C1<0	text	YES
A2:D100	Expression	=AND($C2<>"",$C2<5)	text	NO