change word in cell when date in another cell is past

Tiddle

We have a safety sheet that has all the names of all who have been trained in a certain safety procedure, we have issued them a certificate but that certificate will expire in 3 years. On the sheet we have a trained date column a retrain date column and the 3rd column called status.

In the status column we have it set as a dropdown with "valid" or "needs retraining", when entering their information in the row we manually select "valid", what I would like to do is when the retrain date column reaches the 3rd year date I would like the cell that had "valid" in it to change to "Needs Retraining".... i have tried workflows but just can't seem to make the status column cell change to "needs retraining".

Please help me, thanks in advance.

Kelly Moore

Hey @Tiddle

There are a few approaches that might work for you.

If you decide to let the Status be determined completely by formula (no manual entry) then this formula could be used in the Status column

=IF(TODAY()>=Retrain Date@row, "Valid","Needs ReTraining")

An alternate to the formula above is to use the Change Cell Value automation workflow with When a Date is Reached as your trigger. This approach would allow you to continue to manually update your Status column. In my example below, my Status column is a dropdown column with "Valid" and "ReTrain" being possible responses.

Will either of these work for you?

Kelly

Tiddle

Hello Kelly

Thanks for this.

I like the idea of the formula as it will automatically add either Valid or Needs Retraining without having to input it.

Got it formula that worked is =IF(TODAY() < [Retrain date]@row, "Valid", "Needs ReTraining")

Thank you