Are you able to calculate minutes, seconds, and milliseconds in Smartsheet?

Patty Clark

I have a sheet that I was able to calculate minutes and seconds using this formula.

=VALUE(LEFT(Time@row, FIND(":", Time@row) - 1)) + VALUE(RIGHT(Time@row, 2)) / 60

Now I’m working on a form that needs to include milliseconds. I tried using the above formula and adding milliseconds but my formula returns an #INVALID VALUE error.

=VALUE(LEFT(Time@row, FIND(":", Time@row) - 1)) + VALUE(RIGHT(Time@row, 2)) / 60 + VALUE(LEFT(Time@row, FIND(".", Time@row) - 1) + VALUE(RIGHT(Time@row, 2)) / 1000)

What am I doing wrong?

Thank you for your assistance.

driving.png

Jgorsich

You can't get the timestamps down to that fine, I don't believe. Any native time series calculations are pretty well addressed in this thread:

Formulas for Calculating Time - Smartsheet Community

That being said, you don't really need to do it natively at all. You've got the data entered and you've already walked away from using the various time formulas, so what you are really asking is if you can manipulate two numbers pulled from a string (you can - your original formula shows it - though I think it has a bug) and if you can further handle the decimal portion of the second number separately.

Your original formula, incidentally, is ALREADY handling the milliseconds (once fixed to handle more than 2 digits for seconds) - if you have 2:30.292 that is 2 minutes and 30.292 seconds, correct? So when you translate this, you should be getting 2.5153333 (incidentally, I think this formula:

=VALUE(LEFT(Time@row, FIND(":", Time@row) - 1)) + VALUE(RIGHT(Time@row, 2)) / 60

has a bug. You seem to be trying to do the value of the minutes + the seconds converted to minutes, but this won't get you there if you have anything other than 2 digits. Try this instead:

=VALUE(LEFT(Time@row, FIND(":", Time@row) - 1)) + (VALUE(RIGHT(Time@row, LEN(Time@row) - FIND(":", Time@row)))) / 60

If you want to isolate JUST the decimal portion of the seconds, just do

=IFERROR(VALUE(RIGHT(Time@row, LEN(Time@row) - FIND(".", Time@row))), 0)