Using RYG Symbol Column Based on Two Dates.

CK17
CK17 ✭✭
edited 10/28/21 in Formulas and Functions

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

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

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

«1

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    @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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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
    CK17 ✭✭

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


  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!