Bring back a value based on condition of another cell

Carlos Yanes

I have a date column that based on it's value, it bring back a word.

If the date is older than today, bring back the word 'red'

If the date is within a 14 range of today's date, bring back the word 'yellow'

If the date is greater than 14 days but less than 30 from today' bring back the word 'green'

If the date is more than 30 days from today's date bring back 'blue'

I tried a formula like this one -

=IF([Original Date]2 <> "", IF([Original Date]2 < TODAY(), "Red", IF([Original Date]2 <= TODAY(14), "Yellow", "Green"), "Blue"))

Paul Newcome

=IF([Original Date]@row <> "", IF([Original Date]@row < TODAY(-10), "Red", IF([Original Date]@row <= TODAY(), "Yellow", IF([Original Date]@row <= TODAY(90), "Green", "Blue"))))

Paul Newcome

Slightly different criteria than your last post, so here is a slightly different formula...

=IF([Original Date]@row <> "", IF([Original Date]@row < TODAY(), "Red", IF([Original Date]@row <= TODAY(14), "Yellow", IF([Original Date]@row <= TODAY(30), "Green", "Blue"))))

Carlos Yanes

Just tried it and still getting the invalid operation error. :(

Paul Newcome

Is your [Original Date] column set as a date type column in the column properties?

Carlos Yanes

Yes it is and it's a calculated field based on another, if that makes sense.

This is the formula that I have in that date field -

=IF([Project Target Launch (Original)]@row = "Jan 2020", "1/21/20", IF([Project Target Launch (Original)]@row = "Feb 2020", "2/21/20", IF([Project Target Launch (Original)]@row = "Mar 2020", "3/21/2020", IF([Project Target Launch (Original)]@row = "Apr 2020", "4/21/20", IF([Project Target Launch (Original)]@row = "May 2020", "5/21/20", IF([Project Target Launch (Original)]@row = "Jun 2020", "6/21/20", IF([Project Target Launch (Original)]@row = "Jul 2020", "7/21/20", IF([Project Target Launch (Original)]@row = "Aug 2020", "8/21/20", IF([Project Target Launch (Original)]@row = "Sept 2020", "9/21/20", IF([Project Target Launch (Original)]@row = "Oct 2020", "10/21/20", IF([Project Target Launch (Original)]@row = "Nov 2020", "11/21/20", IF([Project Target Launch (Original)]@row = "Dec 2020", "12/21/20", IF([Project Target Launch (Original)]@row = "Q1", "3/21/20", IF([Project Target Launch (Original)]@row = "Q2", "6/21/20", IF([Project Target Launch (Original)]@row = "Q3", "9/21/20", IF([Project Target Launch (Original)]@row = "Q4", "12/21/20", IF([Project Target Launch (Original)]@row = "H1", "6/21/20", IF([Project Target Launch (Original)]@row = "H2", "12/21/20", IF([Project Target Launch (Original)]@row = "2021", "1/21/2121")))))))))))))))))))

Paul Newcome

The problem is that your formula is not outputting dates. You need to use a DATE function in the output.

DATE(yyyy, mm, dd)

Carlos Yanes

where would i put that to make it work?

Paul Newcome

Wherever you are outputting a date.

Carlos Yanes

yeah but i'm not sure how to integrate that into my existing formula?

Paul Newcome

=IF([Project Target Launch (Original)]@row = "Jan 2020", DATE(2020, 01, 21), IF([Project Target Launch..........................................................................

Carlos Yanes

https://community.smartsheet.com/discussion/comment/257433#Comment_257433

@Paul Newcome Thank you!! It worked perfectly. I'm very happy right now! LOL

One other thing to ask, on the fomula that you provided for the different color formatting, how can i tweak so that blue is 3 or more months out from today's date?

Paul Newcome

So would you shift green from being within 30 days to within 90 days as well? If not, how would you account for that gap?

Carlos Yanes

Thank you. Now that it's working, I think I need to swap around the colors to make more sense for this project. How should I adjust the formula if I'm wanting to show up like this -

16 days or older than today red

15 days greater than today yellow

today's date and into the future 90 days green

anything into the future more than 90 days from today's date blue

Paul Newcome

I'm not sure I understand what you are looking for with your red and yellow criteria. Try adjusting the numbers inside of the TODAY() functions. Negative numbers are in the past and positive numbers are in the future.

Carlos Yanes

I think I almost got it, but can't seem to get the "yellow" to work -

=IF([Original Date]@row <> "", IF([Original Date]@row < TODAY(16), "Red", IF([Original Date]@row <= TODAY(15), "Yellow", IF([Original Date]@row <= TODAY(60), "Green", "Blue"))))

If I have a date of 7/21/2020 then it should bring back yellow, because yellow is within 15 days of today's date.

Paul Newcome

That's because your Red states within 16 days. The formula stops at the 1st true value.