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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!