# Bring back a value based on condition of another cell

Options
✭✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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

«1

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
edited 07/23/20
Options

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

• ✭✭✭✭✭✭
Options

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

DATE(yyyy, mm, dd)

• ✭✭✭✭✭
Options

where would i put that to make it work?

• ✭✭✭✭✭✭
Options

Wherever you are outputting a date.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

@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?

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!