how to group days into months in pivot table app in Smartsheet

Options
Jasmine Hassan
Jasmine Hassan ✭✭✭✭
edited 06/14/22 in Add Ons and Integrations

How can i consolidate a day wise report to month wise report using Pivot table app? I'm looking for similar option present in MS Excel - Pivote table feature.

Or is there any formula which can consolidate data from days to months?

I want something similar to the above excel pivot table in Smartsheet. Here i have consolidated all March/April/May quantity.

Regards,

Jasmine

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Jasmine Hassan

    The way I would personally do this is to set up a helper column in the source sheet which identifies which Month the Date column is in. You can set a column formula to simply say =MONTH([Date Column]@row)


    Then in your Pivot configuration, set the Items to be the Rows and this helper Month column to be the columns in the Pivot sheet. Then COUNT the values in the MONTH column to return your summarized data:


    This a preview of how it would look. Keep in mind that you can update column names to be the month names (instead of just 1, 2, etc) after the Pivot has been created.


    Would this work for you? Here's more information on the Pivot App.

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Jasmine Hassan

    The way I would personally do this is to set up a helper column in the source sheet which identifies which Month the Date column is in. You can set a column formula to simply say =MONTH([Date Column]@row)


    Then in your Pivot configuration, set the Items to be the Rows and this helper Month column to be the columns in the Pivot sheet. Then COUNT the values in the MONTH column to return your summarized data:


    This a preview of how it would look. Keep in mind that you can update column names to be the month names (instead of just 1, 2, etc) after the Pivot has been created.


    Would this work for you? Here's more information on the Pivot App.

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Jasmine Hassan
    Jasmine Hassan ✭✭✭✭
    Options

    Thank you for your help Genevieve P :) Have great day ahead!!

  • Genevieve P.
    Options

    No problem at all! 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Karthik Ramanathan
    Options

    When we want to group dates to month, is there a formula that could for example convert 2021-01-01 to Jan 21 and then 2021-02-01 to Feb 21 and then on and on. I am looking for something more similar and seamless like Excel.

  • Genevieve P.
    Options

    Hi @Karthik Ramanathan

    Yes, you can write a formula to display different text based on the date in the date cell. It will be a long nested-if statement because you'll need to identify the text for each month, like so:

    =IF(MONTH([Date Column]@row) = 1, "Jan " + YEAR(Date Column]@row)

    So for all 12 months:

    =IF(MONTH([Date Column]@row) = 1, "Jan " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 2, "Feb " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 3, "Mar " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 4, "Apr " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 5, "May " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 6, "Jun " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 7, "Jul " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 8, "Aug " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 9, "Sept " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 10, "Oct " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 11, "Nov " + YEAR([Date Column]@row), IF(MONTH([Date Column]@row) = 12, "Dec " + YEAR([Date Column]@row)))))))))))))

    You can apply this as a column formula and then hide it on the sheet.

    Is this what you're looking for?

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now