IF Function Due Date

EvaM

I am attempting to create a project status column that is using the Harvey Balls (red, yellow, green and gray). I am wanting these to automatically change yellow when my due date column is 120 days with the due date, I would like it remain yellow until a date is entered in the completed column, then I would like it to automatically change to gray. 

However if a date is not entered in the completed column and the due date is past 120 days and is 90 days until the due date then I want it to change from yellow to red, and remain red until a date is entered in the completed column, then once the date is entered in the completed column I want it to change to gray. 

 

If the due date column is not within the 120 or 90 days to due date I would like it be green and stay green until the due date becomes 120 days till due date or 90 days till date, or until a date is entered in the completed date column then change to gray. 

 

Can some one help with this? 

thank you,

Eva 

Shaine Greenwood

Hi Eva,

You'll want to place a nested IF statement in the cells of your status column. For example:

=IF(ISDATE([Completed Date]1), "Gray", IF(TODAY() >= ([Due Date]1 - 120), "Yellow", IF(TODAY() >= ([Due Date]1 - 90), "Red", "Green")))

The above function will set the status gray if there's a date value in the "completed date" column. If today's date is greater than or equal to the Due Date - 120 days, set to yellow; if greater than or equal to Due Date - 90 days, set to red. OTHERWISE (when any of those conditions aren't met), set to green.

NOTE: Change the cell references to match the exact column names in your sheet!

More info on IF statements can be found in the help center: https://help.smartsheet.com/function/if

Let me know if you have any questions on this.