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/timeformatsnumberformatsrethinkingtheuxandfunction
I got one formula : =VALUE([Start Hr]2@row) + (VALUE([Start Min]2@row) / 60)
But I am getting #unparseble error.
Ex.
Best Answers

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

I realized I made a mistake on the second formula, if the minutes were single digit it wouldn't differentiate between 1 and 10 in a way formulas could easily recognize.
=ROUNDDOWN([total time]@row / 60) + ":" + if(len(MOD([total time]@row, 60))=1,"0"+MOD([total time]@row, 60),MOD([total time]@row, 60
This should fix the issue.
Answers

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

=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]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 :)

I realized I made a mistake on the second formula, if the minutes were single digit it wouldn't differentiate between 1 and 10 in a way formulas could easily recognize.
=ROUNDDOWN([total time]@row / 60) + ":" + if(len(MOD([total time]@row, 60))=1,"0"+MOD([total time]@row, 60),MOD([total time]@row, 60
This should fix the issue.

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!

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
Categories
Check out the Formula Handbook template!