Formula Help: Auto updating the stars

Tony Cooper · 06/23/21

Hi,

I'm working with a semester type project and would like to have the Star symbol used to represent the semester they are in based on a start date. I tried following the example.. .. but I think I'm missing something. Here is the Formula I'm trying to use, based on another example I found.

=IF([Start Date]@row < TODAY(), "Empty", IF([Start Date]@row < TODAY(+90), "One", IF([Start Date]@row < TODAY(+180), "Two", IF([Start Date]@row < TODAY(+206), "Three", IF([Start Date]@row < TODAY(+365), "Four", "Five")))))

Paul Newcome · 06/24/21

So when you say "Start date to current" you are meaning that the Start Date is in the past # days?

If that is the case, then you should be able to use your existing formula and only have to make a few tweaks.

You are going to want to change all of your "less than" arguments to "greater than" and change all of your positive numbers to negative numbers.

Paul Newcome · 06/23/21

Are you able to spell out how you want the formula to work?

For example, your current formula starts off as

If [Start Date] is in the past, "Empty.

If [Start Date] is in the next 90 days, "One".

If [Start Date] is more than 90 but less than 180 days in the future, "Two".

Tony Cooper · 06/23/21

Hi Paul,

Thanks. Not quite. And now that you have it like that, I can see I'm way off.

Start date in the future = empty

Start date to current is between 0 and 90 = one

Start date to current is between 91-180 = two

ect....

I'm trying to use the start date and the "current date". I hope this makes sense.

Thanks again,

Tony

Paul Newcome · 06/24/21

So when you say "Start date to current" you are meaning that the Start Date is in the past # days?

If that is the case, then you should be able to use your existing formula and only have to make a few tweaks.

You are going to want to change all of your "less than" arguments to "greater than" and change all of your positive numbers to negative numbers.

Tony Cooper · 06/24/21

https://community.smartsheet.com/discussion/comment/295964#Comment_295964

Awesome, that seems to be it. Thank you so much Paul!

Paul Newcome · 06/24/21

Happy to help. 👍️

Formula Help: Auto updating the stars

Best Answer

Answers

Help Article Resources

Categories