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

RAG Based on dates.JPG

Find more posts tagged with

Accepted answers

Jeff Reisman

https://community.smartsheet.com/discussion/comment/366497#Comment_366497

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.

Avinash Chandra

Hi Jeff,

Amazing! this works, Can the blank be Green?

Avinash Chandra

Hi Jeff,

This works for me. You are just a genius. Thanks for your help.

All comments

Jeff Reisman

@Avinash Chandra

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")))

Avinash Chandra

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.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/366497#Comment_366497

If that's the case, just put that entire nest IF formula inside another IF:

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.

Avinash Chandra

Hi Jeff,

Amazing! this works, Can the blank be Green?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/366514#Comment_366514

Absolutely:

Avinash Chandra

Hi Jeff,

This works for me. You are just a genius. Thanks for your help.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.