Using multiple Star symbols based on age of request

Options

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?

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!