Recurring Cleaning Schedule Setup

jedelman

I'm helping my Laboratory Department setup a Tracker for a Cleaning Schedule that has a variety of pieces of equipment that each have a different time range of when they need to be cleaned (from every 2 weeks to 6 months).

I had it in my head that I could use Gannt functions to setup the varying Durations of each piece (i.e. 2w, 30d, etc.).

My thought was then to have the team members check a box when cleaning was completed and then use the automation Record a Date to replace the Last Performed date (thus setting the Next Cleaning Date automatically). After that I figured I could do another automation to Reset the Checkbox after a day or so.

The problem comes with the Record a Date Automation not allowing me change my Last Performed date because I have dependencies enabled. I have no need of the predecessors but the Duration column is very useful in this case.

I'm sure there is a simpler way and I'm just missing it.

marc4

Seems simple enough. Just set up a sheet like this. Next Check is just Last Checked + Interval in Days

Two automations. One sets the last check date to today when the Task Completed is checked.

One unchecks Task completed when Last Checked date changes.

Paul Newcome

Have you thought about turning off dependencies and using Calendar view instead?

jedelman

I like the suggestion but its difficult to visualize with over 100 pieces of equipment all displayed in the calendar.

I'm more looking for a solution that will allow me to assign specific time intervals per piece of equipment as well as reset the cleaning timeline upon completion.

Not sure my description was very good so here is an example.

On or before October 25, 2022 I'd like a team member to check Cleaning Complete when Centrifuge 4 and 5 are clean. I would then like Last Performed to the add the current date and bump out Next Cleaning date to 2 months from now.

marc4

Seems simple enough. Just set up a sheet like this. Next Check is just Last Checked + Interval in Days

Two automations. One sets the last check date to today when the Task Completed is checked.

One unchecks Task completed when Last Checked date changes.

Paul Newcome

Ah. Yes. Calendar view would definitely be very unwieldy for that many.

Is there a reason you have dependencies turned on? You don't need dependencies turned on to show dates on a Gantt view, and with them turned off you should be able to set up the record a date automation.

Edit: You would use a formula to populate the Due Date based on the last completed date and your duration column.

marc4

I was thinking about this some more and abstracted it a bit:

So you have each task. A link to any detailed instructions for forms that need to be filled out. The last check date, the check interval, the next check date (which is last check + interval) the number of days before the check date that the assigned person should get a message, the person assigned, notes.

We have the two automations; one that sets the last check date when the box is checked and one that clears the checkbox when the check date is changed.

Then we have one automation that sends an update request to the assigned person on the notification date with a link to the form to check the box, enter any notes and upload any attachments such as a calibration report.

/marc

jedelman

Thank you both for your insight. I'm not sure why I was so locked in on Dependencies and felt like it was necessary. It clearly wasn't.

I've gone with the solutions to just add the Interval column and have the last performed date be updated by an automation. That and the Completed checkbox undone by an automation.

I was a little surprised there wasn't a template set in place for this type of activity. Its common at every lab I've worked in. It certainly wasn't difficult to make so maybe that is why there isn't a template.

Thank you both again!

Paul Newcome

Glad you were able to find a working solution!