# Calculations based on reaching a date

Options
✭✭✭✭✭
edited 03/09/23

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

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭
Options

Hi Jeff,

Amazing! this works, Can the blank be Green?

• ✭✭✭✭✭
Options

Hi Jeff,

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

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭
Options

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%

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭
Options

Hi Jeff,

Amazing! this works, Can the blank be Green?

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭