Smartsheet bug? Time / Rounding formula error

Jack Parry

Hi, I'm using the following formula to calculate the difference between 2 times:

=IFERROR((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60), " ")

And then using the following calculation to round down the difference following this logic:

=IFERROR(IF(Difference@row < MROUND(Difference@row, 5), MROUND((Difference@row - 5), 5), MROUND(Difference@row, 5)), " ")

However, depending on what start and end times I put in, I get different results:

The difference between the 2 times is exactly the same, yet I'm getting a different result?

Samuel Mueller

@Jack Parry I'm curious if this helps. However this is a very weird situation. Try using the time function to calculate your difference instead

=(TIME([End Time]@row) - TIME([Start Time@row)) * 24 * 60

Paul Newcome

Have you looked at the FLOOR function?

FLOOR Function | Smartsheet Learning Center

https://help.smartsheet.com/function/floor

Rounds a number toward zero to the nearest specified multiple of significance

Samuel Mueller

I don't know, it seems like something is wrong. Even with the floor formula. The "70" value doesn't seem to be 70 actually, as confirmed in the first column where 1 is returned.

Jack Parry

@Samuel Mueller @Paul Newcome

It seems to be the 'Difference' formula, where I'm multiplying by 60 to get the time difference to 'minutes'

When I use =(TIME([End Time]@row) - TIME([Start Time]@row)) * 24 * 60

and then not multiplying and just going to rounding down.

It then seems to work? I'm guessing its a decimal place/rounding error when getting the time difference in minutes. Still very strange!

Samuel Mueller

@Jack Parry yeah there is something off, but if you wrap your time formula in a Round, that should fix all issues

=ROUND((TIME([End Time]@row) - TIME([Start Time]@row)) * 24 * 60)

Hopefully..

Smartsheet should definitely look at this issue.