Dashboard Element, Count By Created, Grouped by Time (Hour)
I'm able to quickly accomplish this in Excel, but have no idea how to achieve the same in Smartsheet. We have a call log where a new Created row is automatically generated on submission. I'd like to create a graph that totals all calls by time, specifically the hour of the day.
In Excel I create a pivot on the Created column, group by Hour, then chart.
How would I accomplish this in Smartsheet?
Best Answer
-
Hi @sstubbs
You can get the time part of the "Created Date" value in 24-hour format with the following formula;
=TIME(RIGHT(Created@row, 8), 1)
Then you can get the "Hour" part by the following formula;
=VALUE(LEFT([24 Hour Format]@row, 2))
Then, using a metric sheet counting data grouping by hour, you can create a chart in a dashboard.😁
Answers
-
Hi @sstubbs
You can get the time part of the "Created Date" value in 24-hour format with the following formula;
=TIME(RIGHT(Created@row, 8), 1)
Then you can get the "Hour" part by the following formula;
=VALUE(LEFT([24 Hour Format]@row, 2))
Then, using a metric sheet counting data grouping by hour, you can create a chart in a dashboard.😁
-
@jmyzk_cloudsmart_jp Thank you for your solution, that worked.
-
Wow, your dashboard is beautiful!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!