Formulas for Calculating Time

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 02/07/24 in Best Practice

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)


Flagging a Date and Time Overlap

How do I create time of day columns?

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