As noted on the Smartsheet CommunityQuestionThere was, so I thought about how to do it.
Question content
The content of the question is
“We use forms in Smartsheet to let people submit requests to our team.A column that automatically generates the time sent (month/day/year/time zone)there is.To justify the team's shift hours,Count how many requests there are in each time periodI need to be able to.Is there a way to calculate how many requests were received for each time period, such as 8am-9am and 9am-10pm? "
That's it.
For example, at a customer center such as a call center, the number of calls and inquiries varies depending on the time of day, so shifts are drawn according to the fluctuations. I think it's a scene that counts.
A column that automatically generates the time (month/day/year/time zone) that the request was sent
This meansCreated OfSystem columnis usually used.
Extract time from creation time data
Next, I need to extract the hour 22 (for a 12-hour clock) from data with a creation time such as 10/15/48 XNUMX:XNUMX.
Smartsheet does not have the ability to extract the time portion from "Created", so you will need to use a character manipulation function such as the "MID" function to pull the time string. (The year can be retrieved with YEAR( ), and the month with MONTH( ).)
For example:
=VALUE(MID(Created@row, FIND(” “, Created@row) + 1, FIND(“:”, Created@row) – FIND(” “, Created@row) – 1)))
The syntax for the MID( ) function that pulls a specific character from a string is
MID( string start position number of characters )
So
First, the format of the date and time of creation is in the form of 22/12/10 15:48.FIND(” “, Created@row) + 1 to find it.
Next, the time part is between "blank" and ":", so the number of characters is
FIND(“:”, Created@row) – FIND(” “, Created@row) – 1
to find it.
The above formula is obtained by substituting the above into MID, but to simplify the operation, replace it with a number using the VALUE( ) function. (This is irrelevant for Japan, but if the creation time is displayed in a format such as 12/14/22 9:36 AM like in the United States, you need to add XNUMX to the time obtained above for PM. )
Count the number of "requests" during a certain period of time
Then the "COUNTIF" function counts the number of "requests" during a certain time period.
For example:
The {Hour} part refers to the request sheet.
=COUNTIF({Hour}, [24 hour format]@row)
A dashboard showing the overall structure
