Group Report by Month and show current month first.
Hi there Community,
I'm trying to pull a report that shows my due dates for all my projects. I want to group the due dates by month. I created a formula that pulls the month name from the due date. If I have a project that's due 6/15/22 and another one due 6/27/22, the formula will pull 06 JUNE for both of them. I then want to sort my report so that anything due this month will show up first, anything due next month will show up second etc...
Any ideas?? When I sort with the above mentioned formula, it puts January first, not the current month.
Thanks
Best Answer
-
Grouping in Reports is done alphabetically so you would need your current month to start with 01 for it to show up first.
I would suggest adjusting your current formula to output a specific number based on if the Month in the date column is today's month.
For example:
=IF(MONTH(Date@row) = MONTH(TODAY()), "01 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 1, MONTH(Date@row) = MONTH(TODAY()) - 11), "02 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 2, MONTH(Date@row) = MONTH(TODAY()) - 10), "03 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 3, MONTH(Date@row) = MONTH(TODAY()) - 9), "04 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 4, MONTH(Date@row) = MONTH(TODAY()) - 8), "05 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 5, MONTH(Date@row) = MONTH(TODAY()) - 7), "06 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 6, MONTH(Date@row) = MONTH(TODAY()) - 6), "07 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 7, MONTH(Date@row) = MONTH(TODAY()) - 5), "08 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 8, MONTH(Date@row) = MONTH(TODAY()) - 4), "09 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 9, MONTH(Date@row) = MONTH(TODAY()) - 3), "10 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 10, MONTH(Date@row) = MONTH(TODAY()) - 2), "11 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 11, MONTH(Date@row) = MONTH(TODAY()) - 1), "12 "))))))))))))
Then if you Filter the Report by the date so that you're only seeing dates in the Future but less than the end of the year. Let me know if this would work for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Grouping in Reports is done alphabetically so you would need your current month to start with 01 for it to show up first.
I would suggest adjusting your current formula to output a specific number based on if the Month in the date column is today's month.
For example:
=IF(MONTH(Date@row) = MONTH(TODAY()), "01 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 1, MONTH(Date@row) = MONTH(TODAY()) - 11), "02 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 2, MONTH(Date@row) = MONTH(TODAY()) - 10), "03 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 3, MONTH(Date@row) = MONTH(TODAY()) - 9), "04 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 4, MONTH(Date@row) = MONTH(TODAY()) - 8), "05 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 5, MONTH(Date@row) = MONTH(TODAY()) - 7), "06 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 6, MONTH(Date@row) = MONTH(TODAY()) - 6), "07 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 7, MONTH(Date@row) = MONTH(TODAY()) - 5), "08 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 8, MONTH(Date@row) = MONTH(TODAY()) - 4), "09 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 9, MONTH(Date@row) = MONTH(TODAY()) - 3), "10 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 10, MONTH(Date@row) = MONTH(TODAY()) - 2), "11 ", IF(OR(MONTH(Date@row) = MONTH(TODAY()) + 11, MONTH(Date@row) = MONTH(TODAY()) - 1), "12 "))))))))))))
Then if you Filter the Report by the date so that you're only seeing dates in the Future but less than the end of the year. Let me know if this would work for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!