How to create metric from timestamp in sheet

Noah Webster

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!

Ben Goldblatt

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

Leibel S

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")

Chris Shifflett

Can you be a bit more specific?

Ben Goldblatt

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

Leibel S

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")

Noah Webster

https://community.smartsheet.com/discussion/comment/268664#Comment_268664

You're a genius thank you so much!!! WOW this is so helpful