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

  • 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.

  • jgneely72151
    jgneely72151 ✭✭✭✭✭

    Not sure what I did but I'm still not having any luck.

    I'm sharing my test sheet with you:

    https://app.smartsheet.com/b/publish?EQBCT=c48dd80e253243e0b9a46a4b0b97d58b

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you copy/paste the exact formula from the Sum column to here?

  • jgneely72151
    jgneely72151 ✭✭✭✭✭

    Sure, I just copied and pasted the formula you dropped above:

    =((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)

  • jgneely72151
    jgneely72151 ✭✭✭✭✭

    and here's my formula for Total Hours:

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

  • jgneely72151
    jgneely72151 ✭✭✭✭✭

    ooh shoot it worked. I just needed to refresh my page. so sorry and thank you for your help. It's exactly what I needed. 🤗

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    Sometimes when copy/pasting a formula, you will need to refresh the sheet to get it to read the column names properly.

  • jgneely72151
    jgneely72151 ✭✭✭✭✭

    Another question....while the 2 formulas are great for 1 day events, how should I write the formula for an event that spans multiple days, such as the case below, which is a 2 day event? I'm trying to not have to enter the event in twice to reflect this. My Total Hours is not showing a realistic picture. Total hours should be 24 since the event spans 2 days at 12 hours each day. This is such a complex formula, there is no way I could have figured this out.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The way the formulas are currently written is that you have the start date/time and the end date/time with the difference between and not necessarily the duration multiplied by the number of days.


    Will you have any end times that will be earlier than the start time such as starting at 9:00pm and ending at 1:00am?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!