Using RYG Symbol Column Based on Two Dates.

CK17

Hello,

I am trying to use an IF statement where if one date is one week or less than another date, to display the yellow symbol. If the first date is after the second date, display red symbol, if it is within 2 weeks or more of the second date, display the green symbol.

I already have a two date formatted columns set up and a symbol column, just need to figure out the formula that will tie these two together.

For example: =IF([Estimated Date]@row <= 7 [Forecasted Date]@row, "Yellow", 0), IF([Estimated Date]@row > [Forecasted Date]@row, "Red", 0), IF([Estimated Date]@row >= 14 [Forecasted Date]@row, "Green", 0)

What am I missing here? I think I am not writing the formula correctly or need to make it more complex with Today() or something, but illustrating my thought process. The two greens were manually entered, no formula.

I get #unparseable with any combo of that variation that I try like the below equation:

=IF([Estimated Date]@row < [Forecasted Date]@row +7, "Yellow", 0), IF([Estimated Date]@row < [Forecasted Date]@row +14, "Green", 0), IF([Estimated Date]@row > [Forecasted Date]@row "Red", 0)

Thank you

Genevieve P.

Hi @Synergy

This has been the most useful image so far! It looks like you want -7 - 0 to be yellow, not + 14!

Try this version?

=IF(AND([Estimated Date]@row < [Forecasted Date]@row, [Estimated Date]@row > [Forecasted Date]@row - 7), "Yellow", IF([Estimated Date]@row <= [Forecasted Date]@row - 7, "Green", "Red"))

Genevieve P.

Hi @Synergy

Your final formula is returning an error because each of the IF statements are being closed off too soon. You'll want to "nest" the IF statements so that it goes right from the value if True into the next IF statement, like so:

=IF([Estimated Date]@row < [Forecasted Date]@row +7, "Yellow", IF(...

You also don't want to have the 0 in your statements, as you're trying to return colours.

Try this:

=IF([Estimated Date]@row < [Forecasted Date]@row +7, "Yellow", IF([Estimated Date]@row < [Forecasted Date]@row +14, "Green", IF([Estimated Date]@row > [Forecasted Date]@row "Red")))

Did that work for you?

Cheers,

Genevieve

CK17

Hello @Genevieve P. ,

Thank you for the insight. I tried what you specified but am still receiving #unparseable. I can see it is referencing the right columns, but no colors display.

=IF([Estimated Date]@row < [Forecasted Date]@row +7, "Yellow", IF([Estimated Date]@row < [Forecasted Date]@row +14, "Green", IF([Estimated Date]@row > [Forecasted Date]@row "Red")))

Genevieve P.

@Synergy

My apologies! When I copied this I left out a comma at the very end, after the [Forecasted Date]@row but before "Red"

Try this:

=IF([Estimated Date]@row < [Forecasted Date]@row +7, "Yellow", IF([Estimated Date]@row < [Forecasted Date]@row +14, "Green", IF([Estimated Date]@row > [Forecasted Date]@row, "Red")))

CK17

Hello @Genevieve P. ,

When I enter the newer formula with the comma above, it does work. However, what I am seeing in terms of color coding doesn't match up with what I am hoping to achieve.

For example if Estimated Date is 1/10/22 and the Forecasted Date is also 1/10/22, I would want to see Red for being less than or equal to or greater than the Forecasted Date, If Estimated Date is 1/11/22 and Forecasted Date is 1/10/22, I would want to see Red for being greater than the Forecasted Date, If Estimated Date is 1/9/22 and Forecasted Date is 1/10/22, I would expect to see Yellow for being within 7 days of the Forecasted Date. If Estimated Date is 12/27/2021 and Forecasted Date is 1/10/22, I would expect to see Green for being 14 days or greater than the Forecasted Date.

In the below screenshot the color coding is with the entered formula, however, Estimated Date is 12/25/21 and Forecasted Date is 1/10/22, I would expect to see Green as 12/25 is 14 days or more from 1/10. 12/3 to 1/9 would be Yellow for being 7 days or less, 1/10 and on would be Red.

Thank you,

Genevieve P.

Hi @Synergy

There are no = signs in your formula, which is why you're not seeing the correct results. Additionally, the order of operations is the wrong way around... IF statements stop as soon as they find a correct match and don't keep reading. This means that we'll want to add your +14 days statement first, and then the Yellow statement.

Try this:

=IF([Estimated Date]@row <= [Forecasted Date]@row +14, "Green", IF([Estimated Date]@row <= [Forecasted Date]@row +7, "Yellow", IF([Estimated Date]@row >= [Forecasted Date]@row, "Red")))

Cheers!

CK17

Hello @Genevieve P.,

Thank you for your help on this, like you said it's now mostly registering Green. It doesn't start picking up Yellow's 7 days out, and it only turns red 15 days after the Forecast date.

Genevieve P.

Hi @Synergy

You're right, we need to add in a second condition to create the timeframe block instead of an open-ended timeframe.

What about this:

=IF(AND([Estimated Date]@row <= [Forecasted Date]@row +7, [Estimated Date]@row > [Forecasted Date]@row +14), "Yellow", IF([Estimated Date]@row < [Forecasted Date]@row +14, "Green", IF([Estimated Date]@row > [Forecasted Date]@row, "Red")))

CK17

Using that formula in each cell added the 1/24/22 as Red, but the rest remained Green.

Genevieve P.

Hi @Synergy

Maybe I have the formula operators the wrong way around for the outcome you're looking for....

Try this?

=IF(AND([Estimated Date]@row >= [Forecasted Date]@row +7, [Estimated Date]@row < [Forecasted Date]@row +14), "Yellow", IF([Estimated Date]@row < [Forecasted Date]@row +14, "Green", IF([Estimated Date]@row > [Forecasted Date]@row, "Red")))

CK17

There is some yellow showing now, though they should all be red as well as the date on the right is after the date on the left.

Genevieve P.

Hi @Synergy

I've gone back to your very first post to re-read what it is you're looking to do.

The formula above will only give you Yellow if it's in that specific window (7 - 14 days), but the red only comes in if the one Date is greater than the other.

Should we also have a statement for the Red that looks at 0 - 7 days? Or should that be yellow, too?

Here's an alternate way of writing the formula:

=IF(AND([Estimated Date]@row >= [Forecasted Date]@row + 7, [Estimated Date]@row < [Forecasted Date]@row + 14), "Yellow", IF([Estimated Date]@row > [Forecasted Date]@row + 14, "Green", "Red"))

CK17

This is what I am trying to achieve if possible:

Estimated Date >= Forecasted Date: Red

Estimated Date < Forecasted Date by 7 Days: Yellow

Estimated Date < Forecasted Date by >= 14 Days: Green

Genevieve P.

Hi @Synergy

What about the gap from 7 days - 14 days?

Estimated Date >= Forecasted Date: Red

Estimated Date < Forecasted Date, from 0 - 7 Days: Yellow

Estimated Date < Forecasted Date over 14 Days: Green

There's a missing statement in there for what's between 7 - 14.

Or do you mean the following:

Estimated Date >= Forecasted Date: Red

Estimated Date < Forecasted Date, between 7 - 14 Days: Yellow

Estimated Date < Forecasted Date over 14 Days: Green

In which case we're missing what to do if the Estimated Date and Forecasted date are within 0 - 7 days of each other.

It may be helpful, as we build the formula, to add an additional column in that simply has this formula:

=[Estimated Date]@row - [Forecasted Date]@row

Then you can see the days inbetween the two dates and that will help us identify where the gaps are.

CK17

I see your point. It would make sense to me to have greater than or equal to as Red still and <1 day up to 14 days as yellow and over 14 days green like you mentioned.

Genevieve P.

OK thanks for clarifying!

Try this then:

=IF(AND([Estimated Date]@row >= [Forecasted Date]@row + 1, [Estimated Date]@row < [Forecasted Date]@row + 14), "Yellow", IF([Estimated Date]@row > [Forecasted Date]@row + 14, "Green", "Red"))