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
 10.9K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!