Time formula

Can anyone assist with writing a formula using military hours? See example sheet fields below.

Tags:

Best Answer

  • Shimanta Roy
    Shimanta Roy ✭✭✭✭
    Answer ✓

    Add a helper column for calculating the duration time in decimal. Name the column as Duration. Now apply the following formula in the Duration column:

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

    Now use the following formula in your Total Time column:

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

    Senior Business Intelligence Analyst
    Augmedix Bangladesh
    (A Commure Company)
    shimanta@augmedix.com

Answers

  • Shimanta Roy
    Shimanta Roy ✭✭✭✭
    Answer ✓

    Add a helper column for calculating the duration time in decimal. Name the column as Duration. Now apply the following formula in the Duration column:

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

    Now use the following formula in your Total Time column:

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

    Senior Business Intelligence Analyst
    Augmedix Bangladesh
    (A Commure Company)
    shimanta@augmedix.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!