Using formulas for Date validation within 7, 14 and 30 days

dsmartsheetuser

Hi,

I am trying to use the following formula: 

=IF[Due Date] <=7, "Red", 

IF[Due Date] > 7 and <=14, "Yellow")

IF [Due Date] > 14 and < 30, "Green")

How do I do that, I tried to create a new column to show the colors, but I keep getting Invalid Operation. 

=IF(([Due Date]1) > 7, (AND([Due Date]1) < 14), "Red"),

IF([Due Date]1) > 14, (AND([Due Date]1) < 30), "Yellow"),

IF([Due Date]1) > 30, "Green")

Any help is much appreciated!

Thank you,

Deepthi

Mike Wilday

It seems like you would want to incorporate a netdays or networkdays formula in there to compare the due-date to today and then if the difference falls in those ranges then flag the cell. Question, what should it be if the due date is in the past or less than 7 days? Should it still be red? 

• https://help.smartsheet.com/function/netdays
• https://help.smartsheet.com/function/networkdays
• https://help.smartsheet.com/function/and

Below is the basic principle of how you should lay it out.

=IF(AND(This is true, This is true), then do this, IF(AND(This is true, This is true), then do this, if none are true do this) 

=networkdays([due date]@row, today()) will give you the number of days that are different from the due date and today. Hope this helps.