How to calculate time in smartsheet

Finops C'ship

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

Paul Newcome

Here is an entire thread dedicated to time based calculations in Smartsheet:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

jmcallister

@Paul Newcome

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

Date Time Task Sheet.xlsx

Paul Newcome

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.

cabbsman

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.

Paul Newcome

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

cabbsman

Thank you!

Terence Garland

Hi @Paul Newcome I have a to do list that has dependencies enabled, when i use the formulas above i have to set my duration to 0 days in order for it to calculate the hours correctly, I want to be able to set my tasks to 1, 2 or 5 days depending on the task, If i set the duration to 1 day the time in hours calculates 11 hours, is there a way that the formulas above can be modified to account for this, if the duration is 2 or more days then the formula should calculate the working hours for the additional days plus the hours between the start and finish times.

Below the duration (t column) is set to 0 days

With duration set to 1 day the results change

Paul Newcome

@Terence Garland There are a number of solutions in the below thread regarding time calculations to include a solution for working days (not sure which page(s) it is on but there is one in there).

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Terence Garland

Ok Thanks Paul, I have tried them all but will keep at it.