HH:MM calculation in Smartsheet

08/11/20
Accepted

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][email protected]) + (VALUE([Start Min][email protected]) / 60)

But I am getting #unparseble error.

Ex.



Best Answers

  • L_123L_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_123L_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_123L_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

  • Hi Rahul

    In your formula:

    =VALUE([Start Hr][email protected]) + (VALUE([Start Min][email protected]) / 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][email protected] :)

Sign In or Register to comment.