Conditional formatting based on the current date?

Haig Assadourian

Hi,

I'd like to highlight tasks (specifically the Start date cell) a color when that date is less than the current date (today) and Completion is 0% or null.  In other words, which tasks were supposed to start but have not yet started.  I realize this is a complex condition that uses two fields but it would be helpful as a PM tool.  

 

SS doesn't seem to allow a date condition to be defined using the current date.  Meaning, it should recognize today's datestamp as part of the condition and not require me to enter a specific date as a new condition every time I want to run it.  

 

Similarly, this would be helpful to apply to Finish dates that are less than the current date.

 

Am I missing something?  If not, this seems to be an enhancement opportunity.

 

Thanks.

 

 

Bryan Anderson

Hi,

Smartsheet can do this.  When you are setting up your Condition, there are two ways to set the criteria for a column.  1) pick from a list, or 2) Define custom criteria.  If you define custom criteria you can select "is in the past", "is in the future", or "is today" from a drop down.

 

checkout step 4 here:

http://help.smartsheet.com/customer/portal/articles/516359-conditional-formatting

 

That step talks about this option.

Polly Peterson

Hello -- I would like to do something similar with conditional formatting.  But I would like to be able to select how far in the past (or future) the condition applies.  Such as apply XYZ formatting if the start date is more than 3 days in the past.  This would require "today" to be variable, so that something like "Start Date is less than Today-3days" would be possible.

 

Any work-arounds for this?

 

Thanks!

Bobby Andres

Polly, conditional formatting can be used to say "if its in the last 3 days, apply this format" but it cannot say, "if its more than 3 days in the past, apply this format". There is a way to do this though...

 

Add a checkbox column to your sheet with a formula that will check the box if the corresponding date is less than today - 3. Like this:

 

=IF([Start Date]13 < TODAY(-3), 1)

 

Then set your formatting rule based on the checkbox column (apply formatting if checkbox is checked).