Group Report by Month and show current month first.

Automations 1

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

Genevieve P.

Hi @Automations 1

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

Genevieve P.

Hi @Automations 1

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