Total Hours

Rook

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.

zbr

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!