How to calculate time in smartsheet
Hi,
How to calculate difference in time in smartsheet
for example
Start Time = 12:23:20 and End Time = 20:23:20
I want the difference between this two time in third column = 8:00:00
Answers
-
Here is an entire thread dedicated to time based calculations in Smartsheet:
-
Thank you very much for your Date/Time/Year sheet. It was very helpful in calculating duration. It would have been helpful to allow export of the sheet. There were some minor corrections required for a few of the formulas, such as Days needed an equal and removal of some spaces in a few others. There also appears to be an issue using 12:00 AM as an additional 12 hours are allocated. Overall this was extremely helpful. Thank you very much. I am now going back and reviewing the logic and doing some final testing. I have attached an excel export noting the discrepancies.
Best Regards,
Jean McAllister
-
Which ones needed tweaking (equals, spaces, etc.)?
As for the discrepancies... The formulas are not built to accommodate that many inconsistencies in data input. In your excel spreadsheet you use three different formats for the same time. 12:00 AM / 00:00 / 24:00.
The formulas in that sheet are designed for 12 hour format with AM/PM. I just double checked the sheet, and everything is working as it should, so if you can provide more details as to your feedback, I'd be happy to look into it and make the corrections as needed so anyone else referencing it has the most accurate solution.
-
Hi @Paul Newcome , I am looking at the Calculating Time Worked for Employees example and trying to type in the formula under the SUM column and Difference column. Could you paste the formulas here so I can copy it into my Smartsheet? I'm going cross-eyed trying to type it in.
-
=INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + (SUM@row - INT(SUM@row)) * 60
=((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)
-
Thank you!