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

Jasmine Hassan

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

Genevieve P.

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

Genevieve P.

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

Jasmine Hassan

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

Genevieve P.

No problem at all! 🙂

Karthik Ramanathan

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.

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