Using multiple Star symbols based on age of request

Mariko Wakefield

Howdy,

I manage an intake process where my colleages fill out a form to request a specific service. In my project dashboard, I want to have symbols to indicate the age of the request. So, 1 star would be equal to a request 1 month of less; 2 stars = request 1-2 months etc. I can't seem to get any formula to work, any suggestions?

Heather Duff

Hi! Try this, where "Start" is the start date column, and "End" is the end date column. The 30, 60, 90, and 120 numbers are the number of days between the two dates.

=IF(ISBLANK(End@row), "", IF(End@row - Start@row <= 30, "One", IF(End@row - Start@row <= 60, "Two", IF(End@row - Start@row <= 90, "Three", IF(End@row - Start@row <= 120, "Four", IF(End@row - Start@row > 120, "Five", ""))))))

If you, instead, want to compare the start date to today, then you'd do this:

=IF(TODAY() - Start@row <= 30, "One", IF(TODAY() - Start@row <= 60, "Two", IF(TODAY() - Start@row <= 90, "Three", IF(TODAY() - Start@row <= 120, "Four", IF(TODAY() - Start@row > 120, "Five", "")))))

Depending on how you want it to look, you may want to reverse the number of stars given to each category. (Instead of tasks that take longer to complete being ranked as Five, have them ranked as One, and the tasks that are 30 days or less would be ranked as Five.

Hope this helps!

Best,

Heather

Andrée Starå

Hi @Mariko Wakefield

I hope you're well and safe!

Try something like this and continue to build on it.

=IF(AND(Date@row >= TODAY(-31); Date@row <= TODAY()); "One")

Depending on your country/region, you'll need to exchange the comma to a period.

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!