Using RYG Symbol Column Based on Two Dates.

CK17

This is what I got, a little closer. I added expected color and estimated - forecasted as you recommended:

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

CK17

Well that was certainly the problem!

Thank you for all of your efforts on this, you deserve a gold star! It appears to be working now as hoped and dreamed for. I should have been able to tweak that myself but your assistance really saved that time.

Genevieve P.

I'm so glad we got there in the end! 🙂

CK17

Hello @Genevieve P. ,

I'm back! Can you advise if / how I can edit the formula so that if no dates are entered it's just blank? I believe this normally would be a ",2, false)" but since we are already distinguishing between the colors, I am having difficulty figuring it out. Thanks!

Genevieve P.

Hi @CK17

No problem! Lets add this as the first statement:

=IF(AND([Estimated Date]@row = "", [Forecasted Date]@row = ""), "", 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")))

Cheers,

Genevieve

CK17

@Genevieve P. ,

That worked perfectly, thank you!

CK17

Hello @Genevieve P., or anyone who is available to answer -

Now that I have been working with this formula for a while (and it's been working great), I'd like to see if I can modify it for the scenario where there is not an entry for the Forecasted Date. When this happens, instead of showing red (as it does now based on there being an Estimated Date), I would like it to show green as we know we are not behind and it's not needed.

Is there an easy way to do this? Right now I have just manually marked it green, but I am pretty sure there is a way to expand the IF statement to include this condition first - If Forecasted Date = blank, green for example. In the last screenshot above, if one of the existing red dots had a blank forecasted date, it would instead be green regardless of the estimated date.

Thank you in advance!

Genevieve P.

Hi @CK17

Yes, we can definitely add this in! You're correct, it would just be another IF statement at the beginning.

=IF(AND([Estimated Date]@row = "", [Forecasted Date]@row = ""), "", IF([Forecasted Date]@row = "", "Green", 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"))))

Cheers,

Genevieve