Date to move ahead 1 year from scheduled due date, unless RYG ball is "Not' Green

Allan Kelsch

Hi,

I am hoping someone could help me figure out how to create a formula to calculate the date, 1 year from a date selected if my RYG ball is "Green". This date would consistently be rolling toward the future unless an RYG ball is "Yellow" or "Red".

I am working on a maintenance sheet, which we will be getting every piece of equipment in our shop on a schedule. Every year at the same time we will be maintenancing these machines, however, I need a way to determine if the machines actually got maintenanced before the formula auto-calculates to the next year. My hope is that it will only roll forward if it's past its scheduled due date, and the RYG ball has been turned "Green", otherwise, it'll stay the same date.

Photo attached for

Year and RYG.PNG

Thank you in advance,

Allan K

Mark Cronk

Hi @Allan Kelsch ,

I'm not sure I understand what you're trying to do so here's a guess.

=IF([machine condition]="green", [schedule due date]@row +365, [schedule due date]@row)

Let me know if that's what you needed. Happy to help further.

Mark

Allan Kelsch

Mark,

Thanks for the response! It's hard trying to explain specifically what it is I am asking for, therefore that formula almost works, but not quite.

I've been going over the formula today and realize I may be confusing the whole process. I may even be able to do the formula with one column, if I can just get the thought process on paper.

We will have over 30 machines that need to be maintenanced in a year. Each one needs to be maintenanced on a different day (same day every year though), spread throughout the year so we do not cause overwork on our technician. One thing we need the sheet to do is to let our tech know if he is overdue on his task.

Such as, if he has been unable to maintenance that particular machine, we need a way to determine that it hasn't been done yet. Therefore I was thinking if the RYG ball is either yellow or red, then the "Next Maintenance" date will still be the same date set as the maintenance date (Say 12/15/20), so he knows that machine has yet to be maintained.

Once he finishes maintenenancing that specific machine, he'll change the RYG ball for "Machine Health" to green, which I am hoping to then have the date for the "Next Maintenance Date" to then switch to the following year. (Example: 12/15/21. It will need to repeat this every year so it is automated. Next year it will read 12/15/21 until he once again changes the RYG ball to "Green", as it will then change to 12/15/22 to show completed.

I don't know if that made any more clarity.