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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!