Month Function Sorting in Report

Hello!

I have a sheet that captures items and their start/end dates in a date format. I am trying to create a report that will group the items by the Month that the item is taking place in chronological order (Ex: All items with the target end date in January would be grouped under "January", followed by all items with the target end date in February grouped under "February", etc.).

Unfortunately I realized that the Month function will only return the number value of the month, rather than the word. To get around that, I created a nestled if function (outlined below), which is working to display the Month value, but now in my report I am struggling to get it to sort to show the Months in the correct order (ie. January, Feb, March, etc.). I even went as far as adding month numbers into my function so that the months will display as "1 - January", with the hopes that I would be able to sort the groups ascending to show in the correct order in the report, but it's still not working correctly. Does anyone have any tips?? I feel like this shouldn't be so complicated. Thanks in advance!!


Formula: =IF(MONTH([Target End Date]@row) = 1, "1 - January", IF(MONTH([Target End Date]@row) = 2, "2 - February", IF(MONTH([Target End Date]@row) = 3, "3 - March", IF(MONTH([Target End Date]@row) = 4, "4 - April", IF(MONTH([Target End Date]@row) = 5, "5 - May", IF(MONTH([Target End Date]@row) = 6, "6 - June", IF(MONTH([Target End Date]@row) = 7, "7 - July", IF(MONTH([Target End Date]@row) = 8, "8 - August", IF(MONTH([Target End Date]@row) = 9, "9 - September", IF(MONTH([Target End Date]@row) = 10, "10 - October", IF(MONTH([Target End Date]@row) = 11, "11 - November", IF(MONTH([Target End Date]@row) = 12, "12 - December"))))))))))))


Best Answer

  • NancyRiccardi
    NancyRiccardi ✭✭
    Answer ✓

    Thanks @Andrée Starå. I actually found another method to hopefully make it a little easier for my stakeholders to view the information.

    I added another column into my source sheet for "Year". In that column I entered the following formula:

    =YEAR([Target End Date]@row)

    I went back to my original Month formula below:

    =IF(MONTH([Target End Date]@row) = 1, "01 - January", IF(MONTH([Target End Date]@row) = 2, "2 - February", IF(MONTH([Target End Date]@row) = 3, "03 - March", IF(MONTH([Target End Date]@row) = 4, "04 - April", IF(MONTH([Target End Date]@row) = 5, "05 - May", IF(MONTH([Target End Date]@row) = 6, "06 - June", IF(MONTH([Target End Date]@row) = 7, "07 - July", IF(MONTH([Target End Date]@row) = 8, "08 - August", IF(MONTH([Target End Date]@row) = 9, "09 - September", IF(MONTH([Target End Date]@row) = 10, "10 - October", IF(MONTH([Target End Date]@row) = 11, "11 - November", IF(MONTH([Target End Date]@row) = 12, "12 - December"))))))))))))

    Then in my report I grouped by Year first and then Month, which makes it a bit easier to view.

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    Unfortunately SS does not have formatting for month only via column format or functions. Just to get your sorting right may I suggest that you use a leading 0 for months with single digits in your formula? Like "01 - January", "02 - February"...

  • Thank you for the suggestion, Sameer! That does help some, but now I'm running into another issue... I have some items that are under January 2023, which technically should be after December 2022 in my chronological report. Any ideas how to add that into the formula/report sorting?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to add the year to the formula output so that it is included in the text string.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thanks @Paul Newcome . Is there a way to make it so that the correct year would be automatically selected? Would I have to do another IF statement?

    Here is the formula I currently have:

    =IF(MONTH([Target End Date]@row) = 1, "01 - January", IF(MONTH([Target End Date]@row) = 2, "2 - February", IF(MONTH([Target End Date]@row) = 3, "03 - March", IF(MONTH([Target End Date]@row) = 4, "04 - April", IF(MONTH([Target End Date]@row) = 5, "05 - May", IF(MONTH([Target End Date]@row) = 6, "06 - June", IF(MONTH([Target End Date]@row) = 7, "07 - July", IF(MONTH([Target End Date]@row) = 8, "08 - August", IF(MONTH([Target End Date]@row) = 9, "09 - September", IF(MONTH([Target End Date]@row) = 10, "10 - October", IF(MONTH([Target End Date]@row) = 11, "11 - November", IF(MONTH([Target End Date]@row) = 12, "12 - December"))))))))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would "add" a space and then the year like so:

    =IF(MONTH([Target End Date]@row) = 1, "01 - January", IF(MONTH([Target End Date]@row) = 2, "2 - February", IF(MONTH([Target End Date]@row) = 3, "03 - March", IF(MONTH([Target End Date]@row) = 4, "04 - April", IF(MONTH([Target End Date]@row) = 5, "05 - May", IF(MONTH([Target End Date]@row) = 6, "06 - June", IF(MONTH([Target End Date]@row) = 7, "07 - July", IF(MONTH([Target End Date]@row) = 8, "08 - August", IF(MONTH([Target End Date]@row) = 9, "09 - September", IF(MONTH([Target End Date]@row) = 10, "10 - October", IF(MONTH([Target End Date]@row) = 11, "11 - November", IF(MONTH([Target End Date]@row) = 12, "12 - December")))))))))))) + " " + YEAR([Target End Date]@row)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thanks so much for your help @Paul Newcome . That did work to build the formula correctly, but unfortunately it still didn't solve the grouping/sorting in my report (screen shot below). Any other ideas?

    I am shocked that this is so difficult. I submitted an enhancement request but I need this more quickly than I assume it will be addressed.



  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @NancyRiccardi

    I hope you're well and safe!

    You'll have to start with the year.

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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.

  • Thank you so much @Andrée Starå ! That did work!! I am still shocked that this is the best way to do it as this is not easy to read in the report, but at least it is sorting correctly now. Thanks again for your help!


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @NancyRiccardi

    Excellent!

    Happy to help!

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with 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.

  • NancyRiccardi
    NancyRiccardi ✭✭
    Answer ✓

    Thanks @Andrée Starå. I actually found another method to hopefully make it a little easier for my stakeholders to view the information.

    I added another column into my source sheet for "Year". In that column I entered the following formula:

    =YEAR([Target End Date]@row)

    I went back to my original Month formula below:

    =IF(MONTH([Target End Date]@row) = 1, "01 - January", IF(MONTH([Target End Date]@row) = 2, "2 - February", IF(MONTH([Target End Date]@row) = 3, "03 - March", IF(MONTH([Target End Date]@row) = 4, "04 - April", IF(MONTH([Target End Date]@row) = 5, "05 - May", IF(MONTH([Target End Date]@row) = 6, "06 - June", IF(MONTH([Target End Date]@row) = 7, "07 - July", IF(MONTH([Target End Date]@row) = 8, "08 - August", IF(MONTH([Target End Date]@row) = 9, "09 - September", IF(MONTH([Target End Date]@row) = 10, "10 - October", IF(MONTH([Target End Date]@row) = 11, "11 - November", IF(MONTH([Target End Date]@row) = 12, "12 - December"))))))))))))

    Then in my report I grouped by Year first and then Month, which makes it a bit easier to view.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 05/12/22

    @NancyRiccardi

    Excellent!

    Yes, that's more elegant and flexible.

    It escaped me when I answered, but I usually prepare for this in my client's solution.

    I add columns for Year, Month, Week, Day, and sometimes for Start and End Dates or similar.

    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!