Bring back result based on condition

Carlos Yanes

Hi all,

Hoping I can get some help with a formula. I have a date column that I want to bring back a "health result" depending on the 'finish' date. For example if the finish date is past today's date then it would be red and there would be different thresholds for each date range. Is this possible? The "health result" is a column that shows color coded red, yellow, green or blue dot"

Thank you!

Paul Newcome

I replied on your other post.

Paul Newcome

It is very possible. What are the rest of the requirements for the other colors?

Carlos Yanes

Red would signify that the finish date is older than today's date

Yellow would signify that the finish date is in two weeks

Green would signify that the finish date is more than two weeks from today's date

Blue would imply that there is no definite date yet

Paul Newcome

Try something like this...

=IF(Finish@row <> "", IF(Finish@row < TODAY(), "Red", IF(Finish@row <= TODAY(14), "Yellow", "Green"), "Blue")

Carlos Yanes

Thank you! One other question - if the date field is going to change to month/year only is there a way to get the formula to work?

Paul Newcome

Are you changing the column to a text/number type or are you adjusting the date display format?

Carlos Yanes

I would probably be adjusting to a text/number column to look like this -

Paul Newcome

You would need to come up with a different criteria set or include a hidden date type column that would establish a date for each row.

Carlos Yanes

So then the best solution would be stick to the actual dates?

Paul Newcome

If you are wanting date based results, then you will need to have dates to reference.

Carlos Yanes

Thanks Paul, appreciate your help. Will try that later today and see how it works out.

Paul Newcome

Happy to help. 👍️

Carlos Yanes

@Paul Newcome If I have this formula -

=IF([Project Target Launch (Original)]@row = "Jan 2020", 1 / 21 / 20)

The end result comes back as a decimal rather than a date and changing the format doesn't fix it. How can I adjust it to display as a date?

Carlos Yanes

https://community.smartsheet.com/discussion/comment/257355#Comment_257355

The formula didn't work. I changed to a different column name, but it gave me the incorrect argument set error.

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

Paul Newcome

Your first formula coming out as a decimal is because the "/" symbol indicates division. You are basically saying to output the result of one divided by twenty-one divided by twenty. To output a date you need to use a DATE function.

DATE(yyyy, mm, dd)

The formula giving the error was a misplaced closing parenthesis. Sorry about that.

=IF(Finish@row <> "", IF(Finish@row < TODAY(), "Red", IF(Finish@row <= TODAY(14), "Yellow", "Green")), "Blue")

Carlos Yanes

Thanks Paul. I tried the revised formula, but am getting an invalid operation error.

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