Formulas for Calculating Time
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
