Bring back a value based on condition of another cell
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"))
Best Answer
-
=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"))))
Answers
-
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"))))
-
Just tried it and still getting the invalid operation error. :(
-
Is your [Original Date] column set as a date type column in the column properties?
-
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")))))))))))))))))))
-
The problem is that your formula is not outputting dates. You need to use a DATE function in the output.
DATE(yyyy, mm, dd)
-
where would i put that to make it work?
-
Wherever you are outputting a date.
-
yeah but i'm not sure how to integrate that into my existing formula?
-
=IF([Project Target Launch (Original)]@row = "Jan 2020", DATE(2020, 01, 21), IF([Project Target Launch..........................................................................
-
@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?
-
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?
-
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
-
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.
-
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.
-
That's because your Red states within 16 days. The formula stops at the 1st true value.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.2K Get Help
- 386 Global Discussions
- 212 Industry Talk
- 445 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
