Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Conditional formatting based on the current date?



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.






  • Bryan Anderson


    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:



    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?



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

This discussion has been closed.