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

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    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?

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!