Calculating time passed from one column to another

Paolo Fernandez · 02/14/23

I would like to calculate time passed from a Start Column '00:00' to an End Column '17:00' for example

This 'Overtime Hours' should be automatic, right now I have to fill it myself.

Jeff Reisman · 02/15/23

@Paolo Fernandez In Smartsheet's world, 04:00 is a text value, and you can't do math on text values. In order to calculate the overtime hours automatically, you first need to convert the text values into number values.

Questions:

Are the hours always complete hours starting and ending on the hour? Or could it run from 04:21 to 09:06. for instance? If it's partial hours and odd start/end times, that changes the complexity - but it's still very much doable.

Does overtime ever start on one day and end on the next? For instance, 22:00 - 04:00?

If overtime is full hours starting and stopping on the hour, the formula is fairly simple. We use the LEFT function to isolate the hours portion of the time entry, and the VALUE function to get the numeric value, then subtract:

=VALUE(LEFT([Overtime End]@row, 2)) - VALUE(LEFT([Overtime Start]@row, 2))

If overtime can cross days, we'll need an IF and an alternate formula:

=IF(VALUE(LEFT([Overtime Start]@row, 2)) > VALUE(LEFT([Overtime End]@row, 2)), (VALUE(LEFT([Overtime Start]@row, 2)) - 12 - VALUE(LEFT([Overtime End]@row, 2))), (VALUE(LEFT([Overtime End]@row, 2)) - VALUE(LEFT([Overtime Start]@row, 2))))

In English: If the value of the start time is greater than the value of the end time, subtract 12 from the start time and then subtract the end time; otherwise, just subtract the start time from the end time. So if the overtime is 22:00 - 04:00 (10pm - 4am,) you'd end up with 22 - 12 - 4 = 6 hours.

Now we get even more complex to deal with partial hours. To do that, we need to convert the whole time value into a number with decimals. For that, we'll need to use the RIGHT function to isolate the minutes, and convert the minutes value - # of minutes out of 60 - into portion of 100. We do that by dividing the minutes by 60. The formula for that part would be:

=VALUE(RIGHT([Overtime End]@row, 2)) / 60

Then we need to add that resulting decimal to the hours portion:

=VALUE(LEFT([Overtime End]@row, 2)) + (VALUE(RIGHT([Overtime End]@row, 2)) / 60)

So for 09:06, you'd end up with 9.10. For 13:47, you'd end up with 13.783.

If we need to go this route, the formula is going to get very long and confusing to look at. For that reason, I would recommend adding two hidden helper columns to calculate the decimal values, OTStart and OTEnd. The you use the values from these helper columns in the IF formula above:

=IF(VALUE(LEFT([OTStart]@row, 2)) > VALUE(LEFT([OTEnd]@row, 2)), (VALUE(LEFT([OTStart]@row, 2)) - 12 - VALUE(LEFT([OTEnd]@row, 2))), (VALUE(LEFT([OTEnd]@row, 2)) - VALUE(LEFT([OTStart]@row, 2))))

With all the above in place, OT from 8:17 - 13:33 would equal 5.267. So we'll still want to round this to two decimal places, and then convert the decimal value into minutes by multiplying by .6:

=INT(IF(VALUE(LEFT([OTStart]@row, 2)) > VALUE(LEFT([OTEnd]@row, 2)), (VALUE(LEFT([OTStart]@row, 2)) - 12 - VALUE(LEFT([OTEnd]@row, 2))), (VALUE(LEFT([OTEnd]@row, 2)) - VALUE(LEFT([OTStart]@row, 2))))) + (ROUND(VALUE(RIGHT(IF(VALUE(LEFT([OTStart]@row, 2)) > VALUE(LEFT([OTEnd]@row, 2)), (VALUE(LEFT([OTStart]@row, 2)) - 12 - VALUE(LEFT([OTEnd]@row, 2))), (VALUE(LEFT([OTEnd]@row, 2)) - VALUE(LEFT([OTStart]@row, 2)))), 3)), 2) * .6)

This says, take the integer portion of the result of the formula and add it to the decimal portion after it's rounded to 2 decimal places and multiplied by .6.

Check your parentheses colors and make sure they match! Good luck!

Jeff Reisman · 02/15/23