Group Report by Month and show current month first.

Automations 1
Automations 1 ✭✭✭✭✭
edited 06/29/22 in Formulas and Functions

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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!