Conditional Formatting to change status?

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.

Find more posts tagged with

Conditional Formatting

Comments

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?

Screen Shot 2019-03-18 at 12.19.56 PM.png

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

Sign in to join the conversation!

Quick Links

Trending discussions

If you switch your Grid View to Table view, can and will your sheet stay as a table view permanently
I ask this question because i have switched my grid sheets before to table view and exited out of the sheet, then when i come back its back in grid view. Just wondering if thats normal. thanks
Column with a restricted list very difficult to edit
Hello, I have a column with a restricted list of 100 entries. When I edit the list and scroll to the 50th entry, as soon as I type to change the entry name the column scrolls to the top and I cannot see what I am typing or deleting. I have to keep scrolling back down to the row to see what I have keyed in. Very annoying…
Change the owner of each item in Smartsheet
Hello everyone, I hope you’re doing well. Could you please share how to change the owner of each item in Smartsheet? Any detailed guidance or best practices would be greatly appreciated. Thank you