Group function in reports is sorting alphabetically rather than by date sort

Jerry Alexander

I have tasks with various dates within a given month. I'm wanting to group all tasks by Month so that I can see all tasks that fall into the respective month and group them in a report and have them sort by the month (Jan, Feb, Mer, etc.) However, SS is sorting by the month name (July, June, September, etc.)

I found the formula to return the Month and year from a date cell, but I cant find one that adds the day as well. My thinking is if the day was added to the Month and year in the same cell, this might fix the problem, but I'm not sure, nor do I even know how to try...... :)

Jerry Alexander

That worked! Thanks for your help!

Andrée Starå

Hi @Jerry Alexander

I hope you're well and safe!

I usually structure it something like this in a so-called helper column to ensure that I can sort it in the right order.

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

Would 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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jerry Alexander

How do I edit your formula to reflect my column? I tried this, but it didn't work. (my date column is "Projected Start".

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

Andrée Starå

@Jerry Alexander

Try this.

=IF([Projected Start]@row <> "", 

IF(MONTH([Projected Start]@row) = 1, "01 January", 

IF(MONTH([Projected Start]@row) = 2, "02 February", 

IF(MONTH([Projected Start]@row) = 3, "03 March", 

IF(MONTH([Projected Start]@row) = 4, "04 April", 

IF(MONTH([Projected Start]@row) = 5, "05 May", 

IF(MONTH([Projected Start]@row) = 6, "06 June", 

IF(MONTH([Projected Start]@row) = 7, "07 July", 

IF(MONTH([Projected Start]@row) = 8, "08 August", 

IF(MONTH([Projected Start]@row) = 9, "09 September", 

IF(MONTH([Projected Start]@row) = 10, " 10 October", 

IF(MONTH([Projected Start]@row) = 11, "11 November", 

IF(MONTH([Projected Start]@row) = 12, "12 December")

Did it work?

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

Jerry Alexander

@Andrée Starå Yes, thanks so much for your help. Is there a way to add the year? If I cross years, the sort will group the months together, right?

Andrée Starå

@Jerry Alexander

Excellent!

You're more than welcome!

For the year part. You can structure it in two ways.

• Another column for the year, and then you can sort/group by year and then a month.

=IFERROR(YEAR(Finish@row), "")

• Combined in one

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

Did that work?

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

Jerry Alexander

That worked! Thanks for your help!

Andrée Starå

@Jerry Alexander

I'm always happy to help!

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