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 timebased 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 timebased 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 timebased 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 timebased 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
Check out the Formula Handbook template!