Calculations based on reaching a date
Hi , Good Evening! Here is a formula that provides me RAG status (Critical Mielstone, Health Delviery Status, see attached screenshot)based on days and percentage complete:
=IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]4 <= 30, [% Complete]4 <= 0.65), "Red", IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]4 <= 30, [% Complete]4 <= 0.75), "Yellow", IF([% Complete]4 = 1, "Blue", "Green")))
The revised planned Finish@row date is 11 Oct 2023 and Revised Planned Finish]4 date is 11 Sep 2023. How to I ensure it does not show RED as it has more than 30 days to reach 11 Sep 2023 and percentage complete is 0%
Kindly help
Best Answers
-
I not sure I understand. You specifically mentioned the "revised planned Finish@row date is 11 Oct 2023 and Revised Planned Finish]4 date is 11 Sep 2023" and in your formula you are subtracting the date in [Revised Planned Finish]4 from the [Revised Planned Finish]@row.
Are you saying that you don't want the formula to test for these values until the current date is equal to or greater than one the of the dates in the formula? If so, which one? (you just said the "1st date", so I'm not clear there.)
If that's the case, just put that entire nest IF formula inside another IF:
=IFERROR(IF(TODAY() >= [Revised Planned Finish]@row, (IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]4 <= 30, [% Complete]4 <= 0.65), "Red", IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]4 <= 30, [% Complete]4 <= 0.75), "Yellow", IF([% Complete]4 = 1, "Blue", "Green")))), ""), "")
This way, until the current date is greater than or equal to the Revised Finish Date on the row, the cell will just be blank. Once the Revised Planned Finish date is reached, the formula will run.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff,
Amazing! this works, Can the blank be Green?
-
Hi Jeff,
This works for me. You are just a genius. Thanks for your help.
Answers
-
Oct 11 2023 minus Sept 11 2023 = 30
So if you don't want to include a difference of 30 days in red or yellow, just remove the = sign from the <= 30:
=IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]4 < 30, [% Complete]4 <= 0.65), "Red", IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]4 < 30, [% Complete]4 <= 0.75), "Yellow", IF([% Complete]4 = 1, "Blue", "Green")))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks Jeff,
The dates that are referenced are part of the schedule and are dynamic. The calculation of 30 days should only happen when we reach the 1st date and currently we are 7 months to reach that date.
The revised planned Finish@row date is 11 Oct 2023 and Revised Planned Finish]4 date is 11 Sep 2023. How to I ensure it does not show RED as it has more than 30 days to reach 11 Sep 2023 and percentage complete is 0%
-
I not sure I understand. You specifically mentioned the "revised planned Finish@row date is 11 Oct 2023 and Revised Planned Finish]4 date is 11 Sep 2023" and in your formula you are subtracting the date in [Revised Planned Finish]4 from the [Revised Planned Finish]@row.
Are you saying that you don't want the formula to test for these values until the current date is equal to or greater than one the of the dates in the formula? If so, which one? (you just said the "1st date", so I'm not clear there.)
If that's the case, just put that entire nest IF formula inside another IF:
=IFERROR(IF(TODAY() >= [Revised Planned Finish]@row, (IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]4 <= 30, [% Complete]4 <= 0.65), "Red", IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]4 <= 30, [% Complete]4 <= 0.75), "Yellow", IF([% Complete]4 = 1, "Blue", "Green")))), ""), "")
This way, until the current date is greater than or equal to the Revised Finish Date on the row, the cell will just be blank. Once the Revised Planned Finish date is reached, the formula will run.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff,
Amazing! this works, Can the blank be Green?
-
Absolutely:
=IFERROR(IF(TODAY() >= [Revised Planned Finish]@row, (IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]4 <= 30, [% Complete]4 <= 0.65), "Red", IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]4 <= 30, [% Complete]4 <= 0.75), "Yellow", IF([% Complete]4 = 1, "Blue", "Green")))), "Green"), "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff,
This works for me. You are just a genius. Thanks for your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
