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

Colin Towner

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?

 

Thanks

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?

Michelle Watkins

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

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.

 

Cheers,

 

Rob.

J. Craig Williams

Rob,

 

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.

 

Craig