# Symbol formula question

Options

Hello! I'm looking for a formula which has the following rules...

There is a sheet with a due date column, a date begun column, a % complete column (manual entry), and an Urgency column which will be the symbols with the formula.

If you are past the due date and the task is not 100% complete, red

If you are between the start date and the due date, check todays date against the % of task you've completed. If you are ahead of the task, green, otherwise, yellow.

So for example if there are 10 days between the date began and the due date, and today's date falls is on day 5, the task should be 50% complete or higher to be green, but if its lower, yellow.

Thank you!

• ✭✭✭✭✭✭
Options

What if the task is at 100% Complete?

• Options

@Paul Newcome then the symbol would be green. Thanks!

• ✭✭✭✭✭✭
Options

Try something like this...

=IF(OR([% Complete]@row = 1, [% Complete]@row >= (TODAY() - [Start Date]@row) / ([Due Date]@row - [Start Date]@row)), "Green", IF(TODAY() > [Due Date]@row, "Red", "Yellow"))

• Options

@Paul Newcome this worked great, thank you! However, if I wanted to convert this to a column formula, it's giving me the "Divide by zero" error when I don't have due dates or percentages input. Is there a way to make the urgency symbol column just show as blank, if there is a row which contains a task, but no information on date or percentage complete? Thank you!

• ✭✭✭✭✭✭
Options

Try this:

=IFERROR(IF(OR([% Complete]@row = 1, [% Complete]@row >= (TODAY() - [Start Date]@row) / ([Due Date]@row - [Start Date]@row)), "Green", IF(TODAY() > [Due Date]@row, "Red", "Yellow")), "")

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!