How to create metric from timestamp in sheet
I am trying to create a metric in my dashboard that shows the average time of day that each contact submitted their request. The specific date is not relevant.
Please help
Thanks!
Best Answers
-
Hi @Noah Webster,
There are ways of extracting character strings from a cell via formulas, such that you could only show the time value from your System Column, but populating an average may be tricky as Smartsheet doesn't currently have built in time tracking options or time-based formulas. You could look into using functions like LEFT, RIGHT, MID, VALUE, FIND in formulas but once you extract the time, you would likely need to break it down even further in separate helper columns to extract the Hour and Minute of the time values, perform another calculation to get a single numeric value to represent the time, and then possibly perform an average based off of the numbers. You would also have to find a way to account for AM and PM values.
I highly recommend Submitting a Product Enhancement Request to see about implementing Time functions and time-based column properties in a future release.
Thanks,
Ben
-
Create column that converts the date time stamp into a number using the formula below:
=IF(SUM(VALUE(LEFT(RIGHT([Created Date]@row, LEN([Created Date]@row) - 9), FIND(":", RIGHT([Created Date]@row, LEN([Created Date]@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT([Created Date]@row, LEN([Created Date]@row) - 9), FIND(":", RIGHT([Created Date]@row, LEN([Created Date]@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID([Created Date]@row, FIND(":", [Created Date]@row) + 1, 2))) + IF(RIGHT([Created Date]@row, 2) = "PM", 720, 0
The below formula will convert a number (averaging the above column for example) into a Time format
=IF([AVERAGE_TIME]@row > 719, IF(ROUNDDOWN(SUM([AVERAGE_TIME]@row - 720) / 60, 0) = 0, "12", ROUNDDOWN(SUM([AVERAGE_TIME]@row - 720) / 60, 0)) + IF(MOD([AVERAGE_TIME]@row, 60) < 10, ":0", ":") + ROUNDDOWN(MOD([AVERAGE_TIME]@row, 60), 0) + " PM", IF(ROUNDDOWN([AVERAGE_TIME]@row / 60, 0) = 0, "12", ROUNDDOWN([AVERAGE_TIME]@row / 60, 0)) + IF(MOD([AVERAGE_TIME]@row, 60) < 10, ":0", ":") + ROUNDDOWN(MOD([AVERAGE_TIME]@row, 60), 0) + " AM")
Answers
-
Can you be a bit more specific?
-
Hi @Noah Webster,
There are ways of extracting character strings from a cell via formulas, such that you could only show the time value from your System Column, but populating an average may be tricky as Smartsheet doesn't currently have built in time tracking options or time-based formulas. You could look into using functions like LEFT, RIGHT, MID, VALUE, FIND in formulas but once you extract the time, you would likely need to break it down even further in separate helper columns to extract the Hour and Minute of the time values, perform another calculation to get a single numeric value to represent the time, and then possibly perform an average based off of the numbers. You would also have to find a way to account for AM and PM values.
I highly recommend Submitting a Product Enhancement Request to see about implementing Time functions and time-based column properties in a future release.
Thanks,
Ben
-
Create column that converts the date time stamp into a number using the formula below:
=IF(SUM(VALUE(LEFT(RIGHT([Created Date]@row, LEN([Created Date]@row) - 9), FIND(":", RIGHT([Created Date]@row, LEN([Created Date]@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT([Created Date]@row, LEN([Created Date]@row) - 9), FIND(":", RIGHT([Created Date]@row, LEN([Created Date]@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID([Created Date]@row, FIND(":", [Created Date]@row) + 1, 2))) + IF(RIGHT([Created Date]@row, 2) = "PM", 720, 0
The below formula will convert a number (averaging the above column for example) into a Time format
=IF([AVERAGE_TIME]@row > 719, IF(ROUNDDOWN(SUM([AVERAGE_TIME]@row - 720) / 60, 0) = 0, "12", ROUNDDOWN(SUM([AVERAGE_TIME]@row - 720) / 60, 0)) + IF(MOD([AVERAGE_TIME]@row, 60) < 10, ":0", ":") + ROUNDDOWN(MOD([AVERAGE_TIME]@row, 60), 0) + " PM", IF(ROUNDDOWN([AVERAGE_TIME]@row / 60, 0) = 0, "12", ROUNDDOWN([AVERAGE_TIME]@row / 60, 0)) + IF(MOD([AVERAGE_TIME]@row, 60) < 10, ":0", ":") + ROUNDDOWN(MOD([AVERAGE_TIME]@row, 60), 0) + " AM")
-
You're a genius thank you so much!!! WOW this is so helpful
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!