Bring back result based on condition

Carlos Yanes · 07/22/20

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 · 07/23/20

I replied on your other post.

Paul Newcome · 07/22/20

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

Carlos Yanes · 07/22/20

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 · 07/22/20

Try something like this...

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

Carlos Yanes · 07/22/20

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 · 07/22/20

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

Carlos Yanes · 07/22/20

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

Paul Newcome · 07/22/20

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 · 07/22/20

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

Paul Newcome · 07/22/20

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

Carlos Yanes · 07/22/20

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

Paul Newcome · 07/22/20

Happy to help. 👍️

Carlos Yanes · 07/22/20

@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 · 07/22/20

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 · 07/23/20

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 · 07/23/20

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

Bring back result based on condition

Best Answer

Answers

Help Article Resources

Categories