How to create a report that summarizes various dates as monthly totals?
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
Answers
-
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)
-
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. -_-
-
You can summarize dates without a helper column. It is grouping them into specific months that requires the helper column.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives