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?

Find more posts tagged with

Dashboards

Time Calculation

Accepted answers

jmyzk_cloudsmart_jp

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.😁

https://app.smartsheet.com/b/publish?EQBCT=9561450106084517a0027e6fd2b37b09

All comments

jmyzk_cloudsmart_jp

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.😁

https://app.smartsheet.com/b/publish?EQBCT=9561450106084517a0027e6fd2b37b09

sstubbs

@jmyzk_cloudsmart_jp Thank you for your solution, that worked.

jmyzk_cloudsmart_jp

@sstubbs

Wow, your dashboard is beautiful!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.