I want my report to show Months in correct order, not alphabetically

Options

I am currently trying to show data as a comparison over the last 2 years but I can't get the data to run in correct month order. I have had a look at rolling monthly data etc but don't understand how that works on the report.

Answers

  • Itai
    Itai ✭✭✭✭✭✭
    Options

    Hi @Iznil76

    Can you share a screenshot? Do you mean the coulmn names are the Months or are the months part of the data?

    Itai Perez

    Reporting and Project Manager

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 07/05/24
    Options

    Hi,

    I hope you're well and safe!

    You have to add a so-called helper column or rename the month's column values (if you have one) to something like 00, 01, 02, etc. You would sort by the year and then the month.

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic day!

    Best,

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

    Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If you are covering multiple years and need each grouping sorted correctly, you would need to use a column in your sheet with a formula to output the year first then the month.

    yyyy-mm

    You can use whatever delimiter you want, but to get it to sort the way you want, it needs to be year first then month. I generally use something along the lines of

    =YEAR([Date Column]@row) + " - " + RIGHT("0" + MONTH([Date Column]@row), 2)

  • dojones
    dojones ✭✭✭✭
    Options

    Here is a formula that gives YY-MM. In case below, Start Date is converted to YY-MM

    =IFERROR(RIGHT(YEAR([Start Date]@row), 2) + "-" + IF(LEN(MONTH([Start Date]@row)) < 2, "0" + MONTH([Start Date]@row), MONTH([Start Date]@row)), "")

  • Iznil76
    Iznil76 ✭✭✭
    Options

    Hello all,

    Thanks for the replies, I will go through each and see which works best. I would like to do a 12 month rolling report ideally, but the month dates don't seem to be able to be put in the actual date order and use alphabetical order instead. I think if I can number them and probably use some sort of helper column I can rectify that, it's just how I get the report to look at that column rather than a date column that I struggle with.

    Thanks again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!