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:

Formulas for Calculating Time

Paul Newcome

Jun 8, 2020

I have decided to create a post that consolidates all of the different time based solutions that I have put together.

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.

I thought it would be helpful to have them all in one place instead of spread out.

I welcome all feedback, suggestions, and additions.

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.

I will also try to post various tips and tricks as I think of them as well such as:

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))

Calculating Time Worked for Employees

Can you calculate time in Smartsheet?

Conversion of timezones (Solved Formula Included)

Date/Time/Year

Flagging a Date and Time Overlap

How do I create time of day columns?

Need to create a "shift" column from a time column

---

View Post

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

Uploaded Jun 11, 20236.07 KB

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

Formulas for Calculating Time

Paul Newcome

Jun 8, 2020

I have decided to create a post that consolidates all of the different time based solutions that I have put together.

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.

I thought it would be helpful to have them all in one place instead of spread out.

I welcome all feedback, suggestions, and additions.

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.

I will also try to post various tips and tricks as I think of them as well such as:

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))

Calculating Time Worked for Employees

Can you calculate time in Smartsheet?

Conversion of timezones (Solved Formula Included)

Date/Time/Year

Flagging a Date and Time Overlap

How do I create time of day columns?

Need to create a "shift" column from a time column

---

View Post

Terence Garland

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