Month Function Sorting in Report

NancyRiccardi

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"))))))))))))

NancyRiccardi

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.

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"...

NancyRiccardi

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

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

NancyRiccardi

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

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)

NancyRiccardi

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å

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!

NancyRiccardi

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å

@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!

NancyRiccardi

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å

@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.