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

colemanj26 · Feb 13, 2023

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 · Feb 13, 2023

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

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

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 · Feb 13, 2023

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