Total Hours
Hello! I would really appreciate it if someone could give me any ideas on what formula I should use to calculate the total hours. Please see the sheet I am working off of. I have a form that people fill out when they get into work and clock in. I use the same form for when they clock out. I am confused as to how to do this.
Answers

Hi Rook,
My experience has been that Smartsheet isn't the best with time calculations, especially when dealing with 12 hour time formats. I built out this work around for a similar sheet I have that utilizes a start time and end time (in your case clock in and clock out times). You will need to build out additional columns to 'extract' values from your Clock In and Clock Out cells.
Not sure if you need to extract and then pull out the numeric value but here's what I have use to get total time (I'll try to use your column names so hopefully it's plug and play)
New Columns:
Clock In Hour
=IF(LEN([Clock In]@row) < 5, LEFT([Clock In]@row, 1), LEFT([Clock In]@row, 2))
Clock In Hour Value
=VALUE([Clock In Hour]@row
Clock In Minutes
=RIGHT([Clock In]@row, 2)
Clock in Minutes Value
=VALUE(Clock In Minutes@row)
Clock Out Hour
=IF(LEN([Clock Out]@row) < 5, LEFT([Clock Out]@row, 1), LEFT([Clock Out]@row, 2))
Clock Out Hour Value
=VALUE([Clock Out Hour]@row
Clock Out Minutes
=RIGHT([Clock Out]@row, 2)
Clock Out Minutes Value
=VALUE(Clock Out Minutes@row)
This should get you numeric values for your clock in and clock out times. Now you have to sort of mash them together to get around the 12 hour clock dilemma.
Hour Calculation
=IF([Clock In Hour Value]@row > [Clock Out Hour Value]@row, ([Clock Out Hour Value]@row + 12)  [Clock In Hour Value]@row, [Clock Out Hour Value]@row  [Clock In Hour Value]@row)
Minute Calculation
=[Clock In Minutes Value]@row  [Clock Out Minutes Value]@row
Final Minutes Calculation
=IF([Clock In Minutes Value]@row < [Clock Out Minutes Value]@row, ([Clock In Minutes Value]@row  [Clock Out Minutes Value]@row) + 60, [Clock In Minutes Value]@row  [Clock Out Minutes Value]@row)
Final Hour Calculation
=IF([Minute Calculation]@row < 0, [Hour Calculation]@row  1, [Hour Calculation]@row)
This should give you your values for total hours and minutes...
Total Time
=[Final Hour Calculation]@row + " Hour(s) : " + [Final Minute Calculation]@row + " Minute(s)"
using this formula will display this format:
7 Hour(s) : 30 Minute(s)
You can then hide all the columns and just display the Total Time column. Hopefully this helps!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!