How to create a report that summarizes various dates as monthly totals?

JPJPJP · 04/01/22

I have a Project Tracker sheet where we keep track of all our projects, their status, if it's been invoiced, and the date of the invoice.

In Excel and Google Sheets, we can easily create PivotTables to summarize all the dates of the projects marked as 'invoiced' by month, so we can see the the monthly and annual invoiced total.

I'm having trouble figuring how to do this Smartsheet? I can't purchase any additional apps or services, so hoping I can use the existing Report or other baked in functionality to accomplish this?

The invoiced dates in the project tracker are regular date formats (4/1/2022). And in the report we want them to be like:

Month # projects $ invoiced

January 2022 X $XXXX

February 2022 X $XXXX

March 2022 X $XXXX

April 2022 X $XXXX

....

Annual Total: XXX $XXXXX

Paul Newcome · 04/01/22

My suggestion would be a helper column that outputs "yyyymm" on each row. Then you can create the report, group by the helper column and use the report summary feature to get your totals.

=YEAR([Date Column]@row) + "" + RIGHT("00" + MONTH([Date Column]@row), 2)

JPJPJP · 04/08/22

https://community.smartsheet.com/discussion/comment/324824#Comment_324824

Thanks for the suggestion! So just to clarify, there's no built in way to summarize dates like this using Reports or Dashboards? Both Excel and Google Sheets can do this easily, so it's strange such as simple task can't be done without yet another helper column in addition to all the other helper columns I had to add for other basic functions. -_-

Paul Newcome · 04/08/22

You can summarize dates without a helper column. It is grouping them into specific months that requires the helper column.

How to create a report that summarizes various dates as monthly totals?

Answers

Categories