# How to create metric from timestamp in sheet

Options

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.

Thanks!

• ✭✭✭✭✭✭
Options

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

• Options

Can you be a bit more specific?

• ✭✭✭✭✭✭
Options

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

• Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!