Having a tough time with time calculations

Nick Amsler

Hello Everyone,

I have been referencing the Time calculation post:

How do I create time of day columns? - Smartsheet.com

For some reason the very last formula listed below returns all the minutes as "00" instead of the actual minutes. I am basically copying the formulas listed in the above link except I never have to worry about dates as they will always be the same date.

The formula I am using is for the last column is:

=IF(VALUE(IF(FIND(".", [Time Calc]@row) > 0, LEFT([Time Calc]@row, FIND(".", [Time Calc]@row) - 1), [Time Calc]@row)) < 10, "0") + IF(FIND(".", [Time Calc]@row) > 0, LEFT([Time Calc]@row, FIND(".", [Time Calc]@row) - 1), [Time Calc]@row) + ":" + IF(FIND(".", [Time Calc]@row) > 0, IF(VALUE(RIGHT([Time Calc]@row, LEN([Time Calc]@row) - FIND(".", [Time Calc]@row) + 1)) * 60 < 10, "0" + VALUE((RIGHT([Time Calc]@row, LEN([Time Calc]@row) - FIND(".", [Time Calc]@row) + 1)) * 60, VALUE(RIGHT([Time Calc]@row, LEN([Time Calc]@row) - FIND(".", [Time Calc]@row) + 1)) * 60), "00"))

For example, for the first row my end should come out to be 00:35, or thirty-five minutes. It just seems the minutes are not being calculated correctly. At least for me this is a very complicated formula that I cannot see where it is going wrong. Any help would be appreciated. Honestly, it's surprising Smartsheet hasn't built in a time format yet into the available options for columns.

Thanks in advance!

Mike TV

@Nick Amsler

Your formula has a parenthesis out of place. If you notice, your formula has two closing parenthesis at the end. The formula in the sheet you're following just has 1 closing parenthesis at the end. When I created a test sheet just now their formula works and yours gives me the same :00 minutes. I suggest copy and pasting the final formula into your sheet again. That should work but if it doesn't and you end up with double closing parenthesis again, then carefully look to see where else the parenthesis are out of order and manually correct.

Mike TV

@Nick Amsler

Your formula has a parenthesis out of place. If you notice, your formula has two closing parenthesis at the end. The formula in the sheet you're following just has 1 closing parenthesis at the end. When I created a test sheet just now their formula works and yours gives me the same :00 minutes. I suggest copy and pasting the final formula into your sheet again. That should work but if it doesn't and you end up with double closing parenthesis again, then carefully look to see where else the parenthesis are out of order and manually correct.

Mike TV

@Nick Amsler

Additionally, I think you may need to add in a ROUND function to the formula because in my sheet I ended up with this:

Nick Amsler

@Mike TV

Thank you! I fixed the parenthesis just in case anyone is wondering it was here:

=IF(VALUE(IF(FIND(".", [Time Calc]@row) > 0, LEFT([Time Calc]@row, FIND(".", [Time Calc]@row) - 1), [Time Calc]@row)) < 10, "0") + IF(FIND(".", [Time Calc]@row) > 0, LEFT([Time Calc]@row, FIND(".", [Time Calc]@row) - 1), [Time Calc]@row) + ":" + IF(FIND(".", [Time Calc]@row) > 0, IF(VALUE(RIGHT([Time Calc]@row, LEN([Time Calc]@row) - FIND(".", [Time Calc]@row) + 1)) * 60 < 10, "0" + VALUE((RIGHT([Time Calc]@row, LEN([Time Calc]@row) - FIND(".", [Time Calc]@row) + 1)) * 60, VALUE(RIGHT([Time Calc]@row, LEN([Time Calc]@row) - FIND(".", [Time Calc]@row) + 1)) * 60), "00"))

So how would I add a "round" to this formula? I can't find any really helpful information on that within the community when it is this complicated of a formula already. At least to me. :)

And thanks again!

Mike TV

@Nick Amsler

=IF(VALUE(IF(FIND(".", [Time Calc]@row) > 0, LEFT([Time Calc]@row, FIND(".", [Time Calc]@row) - 1), [Time Calc]@row)) < 10, "0") + IF(FIND(".", [Time Calc]@row) > 0, LEFT([Time Calc]@row, FIND(".", [Time Calc]@row) - 1), [Time Calc]@row) + ":" + ROUND(IF(FIND(".", [Time Calc]@row) > 0, IF(VALUE(RIGHT([Time Calc]@row, LEN([Time Calc]@row) - FIND(".", [Time Calc]@row) + 1)) * 60 < 10, "0" + VALUE(RIGHT([Time Calc]@row, LEN([Time Calc]@row) - FIND(".", [Time Calc]@row) + 1)) * 60, VALUE(RIGHT([Time Calc]@row, LEN([Time Calc]@row) - FIND(".", [Time Calc]@row) + 1)) * 60), "00"), 0)

Just only use this if you're having the same problem as me shown in my screenshot above. This corrected my test sheet to look like this:

Nick Amsler

@Mike TV

I appreciate the help, unfortunately it keeps coming back as "invalid operation". I even copied and pasted your exact formula above and it comes back the same "invalid operation". I am not sure if I am missing a parenthesis somewhere or its not recognizing something that should be there. Still, I really appreciate the time you've taken to help me with this issue.

Mike TV

@Nick Amsler

Strange. I'm not sure what's wrong because it's working in my sheet. Maybe someone else can figure it out. I just started a large project at work so I cannot spend time on this. I'd probably need access to your sheet anyways to figure it out because as I said it's working for me.