Formula Help: Auto updating the stars

Tony Cooper

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

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

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

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

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

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

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

Paul Newcome

Happy to help. 👍️