If and date functions; sorting rows by priority

Tomer

Hi,

I am attempting to create a task priority column that is using the Harvey Balls (red, yellow, green and gray).

I am wanting these to automatically change to:

1. Gray - if the date today is grater than 60 days from my due date column or if my status column has been changed to "Complete"

2. Green - if the date today is between 60-30 (including) days from my due date column

2. Yellow - if the date today is between 29-15 (including) days from my due date column

3. Red - if if the date today is between 14-date today (including) OR if date on the due date column has passed.

also, can the rows on the sheet be automatically sorted based on this priority column? (all Red priority rows being top of the sheet, then yellow, green, gray).

Can some one help with this? 

thank you,

Tomer

Paul Newcome

Here is your formula:

 

=IF([Due Date]@row <= TODAY(14), "Red", IF([Due Date]@row < TODAY(30), "Yellow", IF([Due Date]@row <= TODAY(60), "Green", IF(OR([Due Date]@row > TODAY(60), Status@row = "Complete"), "Gray", ""))))

 

And you can sort by color. What you're looking for would be ascending order. That will put it with Red on top and Gray on bottom. It is not an automatically updating feature though. As things change/get added/get deleted, you will need to resort periodically.

 

Or you could pull a report from the sheet. The report does auto-sort, but is not editable like the sheet is.

 

The other nice thing about the sheet is that you can set it up to send a notification to whoever is in the contact column for that row whenever a status changes to "Red" (or whatever other criteria you want to send it out on).