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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!