Symbol formula question

Shannon Scarbrough

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!

Paul Newcome

What if the task is at 100% Complete?

Shannon Scarbrough

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

Paul Newcome

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"))

Shannon Scarbrough

@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!

Paul Newcome

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")), "")