HH:MM calculation in Smartsheet

I need to do a time motion study of a process, so will use HH:MM format to get total time in minutes, I am finding it difficult in smartsheet, could you please help me how can I easily subtract Start time and finish time to get total time…

I searched on smartsheet community, on this link : https://community.smartsheet.com/discussion/44746/time-formats-number-formats-rethinking-the-ux-and-function

I got one formula : =VALUE([Start Hr]2@row) + (VALUE([Start Min]2@row) / 60)

But I am getting #unparseble error.

Ex.



Best Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/11/20 Answer ✓

    =(ROUNDDOWN([End Time]@row, 0) - ROUNDDOWN([Start Time]@row, 0)) * 60 + ([End Time]@row - ROUNDDOWN([End Time]@row, 0) - ([Start Time]@row - ROUNDDOWN([Start Time]@row, 0))) * 100


    Give this one a try to get your total minutes easier with less helper columns. Give me a bit and i'll write out the other formula to convert this into hours:minutes

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/11/20 Answer ✓

    =(ROUNDDOWN([End Time]@row, 0) - ROUNDDOWN([Start Time]@row, 0)) * 60 + ([End Time]@row - ROUNDDOWN([End Time]@row, 0) - ([Start Time]@row - ROUNDDOWN([Start Time]@row, 0))) * 100


    Give this one a try to get your total minutes easier with less helper columns. Give me a bit and i'll write out the other formula to convert this into hours:minutes

  • L_123
    L_123 ✭✭✭✭✭✭

    =ROUNDDOWN([total time]@row / 60) + ":" + MOD([total time]@row, 60)

    This will convert it into the correct format. you can change the column to whichever you like

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi Rahul

    In your formula:

    =VALUE([Start Hr]2@row) + (VALUE([Start Min]2@row) / 60)

    It's either VALUE([Start Hr]2) + (VALUE([Start Min]2) / 60)

    or VALUE([Start Hr]@row) + (VALUE([Start Min]@row) / 60)

    You can't have [Start Hr]2@row :)

  • Csuzi
    Csuzi ✭✭

    Hello, I made a timesheet and wage calculation table.

    The data is sent by employees using a form.


    I have a problem with the time calculation.🙄


    Here is the sheet.

    Below is what I don't understand and I don't know where the error is.

    I would be grateful if someone could check it. Thanks!

  • Csuzi
    Csuzi ✭✭

    Sorry, but I messed up the example in my previous post. It shows the end time incorrectly. Below is the corrected one.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!