Using RYG Symbol Column Based on Two Dates.
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
-
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"))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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")))
-
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")))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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,
-
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!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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")))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Using that formula in each cell added the 1/24/22 as Red, but the rest remained Green.
-
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")))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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"))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
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.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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"))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!