Clear check-box, without clearing associated Due Date columns

Linda McShane

Hi

I'm setting up a sheet for reoccurring tasks that have different intervals.

I have a task column, interval column, last check, next check and a check- box.

When the checkbox is ticked the Last check updates to todays date and this updates the Next check date, calculated from the number of days in the interval column.

Is there a way to lock these dates, so that if the Checkbox is un-checked again either manually or automatically it doesn't clear everything? I've tried various formulas and automated workflows and I cant figure it out!

Thanks,

Linda

Paul Newcome

You would need to set up a copy row automation to capture the data in a static sheet and then use a MAX/COLLECT to pull in the most recent date for that task.

Linda McShane

Hi

Is it possible to do it within the one sheet? I'm trying to make this as simple as possible for users; if they have to tick the check- box on one sheet and then use another sheet to view the updated dates it will get confusing.

Thanks

Paul Newcome

Users would only need to use the one sheet. The second sheet is only for capturing the static data which can be pulled into the first sheet using a formula.

I just thought of another possible solution that would be easier to set up...

Use a Record a Date automation to record the date when the box changes to checked. Then use a Clear Cell Value automation to clear out the checkbox when the last checked date changes (because of the automation).