How to Count Requests Submitted within a Certain Time Period
Hi!
I am using a Smartsheet Form for users to submit requests to my team. I have a column that automatically generates when the request was submitted (month/day/year time of day). I need to be able to count how many requests we are getting for each hour of the day to justify shift hours for my team. Is there a way to calculate how many requests we've received at between 8:00 AM and 9:00 AM, 9:00 AM and 10:00 PM, and so on...?
Best Answer
-
Smartsheet does not have a function to extract the time part from "Created," so use a character manipulation function such as the "MID" function to pull the time string.
example:
=VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - FIND(" ", Created@row) - 1))
Then, the "COUNTIF" function counts the number of "Requests" during a specific period.
example:
=COUNTIF({Hour}, [24-Hour format]@row)
Please take a look at the attached dashboard.
https://app.smartsheet.com/b/publish?EQBCT=6a724ee87dde4bf08414ff4884a022be
Answers
-
Smartsheet does not have a function to extract the time part from "Created," so use a character manipulation function such as the "MID" function to pull the time string.
example:
=VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - FIND(" ", Created@row) - 1))
Then, the "COUNTIF" function counts the number of "Requests" during a specific period.
example:
=COUNTIF({Hour}, [24-Hour format]@row)
Please take a look at the attached dashboard.
https://app.smartsheet.com/b/publish?EQBCT=6a724ee87dde4bf08414ff4884a022be
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!