Using ISBLANK with schedule health function

Hello,

I'm using the below formula to calculate schedule health. The schedule delta is based off calculating the difference between columns Target Start Date, Target End Date and Actual Start Date, Actual End Date.

=IF([Schedule Delta (%)]@row > 0.25, "Red", IF([Schedule Delta (%)]@row < 0.1, "Green", "Yellow"))

It works fine, but in the case actual start date is blank, it always shows schedule health as green. I would like it to modify this to say if Actual Start Date is blank, then if target start date is today's date is today or tomorrow schedule health is green, if target start date is 2-4 days from today then schedule health is yellow, and if today's date is 5 or more days from target date then schedule health is red.

Does anyone know how to add this logic into my initial formula above?


Thanks!

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Christine Osborn

    Simply add an IF statement prior to your formula.

    =IF(ISDATE([Actual Start Date]@row, IF([Schedule Delta (%)]@row > 0.25, "Red", IF([Schedule Delta (%)]@row < 0.1, "Green", "Yellow")), "")

    That will check if Actual Start Date is a date, if not, it'll return a blank cell.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!