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.

Want to track when actual completion date is greater than planned due date

Colin Towner
edited 12/09/19 in Archived 2016 Posts

I have set up a column to track the actual task completion date.  I'm trying to set up a flag/conditional format where I can quickly see when tasks have been completed later than planned.  I can't seem to set up a conditional format formula in the "Completion Date" column.  How would you do this?




  • Simone Hill

    Yes, conditional formatting doesnt let you compare cells, you will probably need to do an if statement for the first cell and copy down for the rest of the column.


    in the help centre, search for "using formulas" and if statements are outlined there.


    Basically, you'll probably need to add another column to track if it's late or early. In that column put your if statement to say if completed date is less than due date, do this action, or do that. Then you can use your conditional formatting to format rows based on this additional column.  


    does that help?

  • Hi Simone - what you shared with Colin would be exactly what I would recommend as well.  I typically have a start date variance and an end date variance column comparing the Target Start with Actual Start and Target End with Actual End (if late then negative #, if on time then 0, if ahead of schedule then positive #).


    In the formulas you can then also drive the color of the cell - for example

    * if late then RED * if ahead or ontime then GREEN. Makes for a great heat map in a dashboard as well.

    * we are actually leveraging the logic of the date variance in conjunction with a task schedule status column to display 'on track, at risk or late' and 'green, yellow or red' as the cell color for task schedule status.

  • Rob Hagan
    Rob Hagan ✭✭✭

    Hi Simone, Michelle and Colin,


    It may also be a good plan to put defensive code in to check that the dates are actually present =NOT(ISBLANK(...)) and that they are really dates =ISDATE(...). It appears that you can type non-date text (e.g. "mouse") into a field of type Date/Time and SmartSheet doesn't reject that data.





  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 12/26/16



    Being able to have non-dates in date fields is a feature, not a bug.


    I use 'defensive code' (I hadn't heard that term before) judiciously in Smartsheet, as I have seen performance impact from too many complex formulas. But if the date is likely to be empty, I'll make sure there isn't an error thrown without being caught.



This discussion has been closed.