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

Tomer

thank you very much. 

most of it works well, two points:

1. when applying the formula to all rows for the Priority column, the priority row gets a red - even before I have added a due date for that task. Can the cell remain blank or start with Gray until I actually add the due date for the task?

2.  switching Status to Complete does not change priority status to Gray. for example, a task is 3 days before deadline, but was completed before the deadline. priority should switch to gray from red once I have marked it as complete. 

thanks for the other tips!

Tomer 

 

Paul Newcome

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

 

Here you go... My apologies.

Tomer

this is great help Paul, much appreciated. 

Paul Newcome

No worries. Glad we got it figured out for you.