Conditional Formatting to change status?

scott williams

Hi,



I'd like to be able to change the row status (planning/in progress/complete/overdue) depending on a required date in another column in that same row.



I have a report set up to report all overdue tasks and I'ld like to automate it more to change the status of all overdue tasks without any input from me.



Is this possible?  I've explored the conditional formatting options but nothing seems to apply.



Thanks.

Scott.

 

Andrée Starå

Hi Scott,

It's possible to set up with an IF formula or multiple IF formulas depending on your need and sheet structure.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

scott williams

Hi Andree,

Thanks for the quick reply, I always forget about the ability to program cells.



Here's a screen shot of the sheet I'm working on.  I'd like to be able to change the status of a row to overdue if either the required date or delivery date is past due.

Here's a screenshot, sorry not sure how to share the sheet?



 

Andrée Starå

Happy to help!

Try this.

This will show Overdue when true or nothing if it's not true. If you change the Status manually it will delete the formula in that cell. 

Maybe you'd want to build the formula to change the all the status types automatically?

=IF(OR([REQUIRED DATE]@row <= TODAY(); [DELIVERY DATE]@row <= TODAY()); "Overdue"; "")    

The same version but with the below changes for your and others convenience.    

=IF(OR([REQUIRED DATE]@row <= TODAY(), [DELIVERY DATE]@row <= TODAY()), "Overdue", "")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Best,

Andrée