Frustrated: Found formula for calculating time here in the community

So i found this formula and it is giving me #unparseable. Not sure what I'm doing wrong:

=INT(SUM@row)+":"+IF((SUM@row - INT(SUM@row))*60<10,"0")+(SUM@row - INT(SUM@row))*60

I have 3 columns:

Start End Total Hours

08:00 13:00

I'm using military time and I need to calculate total hours.

Please let me know what I'm doing incorrectly.

Thanks!😕

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @jgneely72151 ,

    You need to create a column [sum] with this formula in it:

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

    Then the formula you posted should work in your [total hours] column.

    Glad you found @Paul Newcome 's time post. It's the best reference to help with time calculation. He is the wizard.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!