Using ISBLANK with schedule health function

Christine Osborn

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!

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.