Calculating total hours worked in a shift

JulianaD · 12/18/18

I'm trying to create a weekly work schedule for all of the employees at our company, so that we can see the start and end of shift and total hours worked per day and per week for each employee.

I contacted the Smartsheet team only to find out that Smartsheet doesn't currently support time based formulas (which is such a a disappointment since so much of what we wanted to be able to do with the program involves reconciling cost AND time and pulling weekly hour information to properly schedule tasks by employee and department).

I've looked around a bit in the community to see what the workaround to the issue would be but I can't quite find something that works (or I may be using the information I found incorrectly).

I've attached a screenshot of the schedule I'm trying to create on Smartsheet and it's equivalent on Excel. The two columns for lunch time are normally hidden. I included them for this purpose since that time needs to be subtracted from the total hours worked and will need to be part of the equation.

Smartsheet Schedule.jpg

Excel Schedule.jpg

Paul Newcome · 12/18/18

Time calculations in Smartsheet can get pretty tricky. How are the lunch times being calculated? Will it ALWAYS be 30 minutes or can it possibly 31 or 29 or something else?

Shawn R · 12/19/18

Depending on your familiarity you could use military time, would be easy to write your formulas in that manner. Would eliminate the ":" for separating hours and minutes and you wouldn't need to worry about the issues of noon. Would be a pretty basic calculation that way, until Smartsheet implements better time formulas.

Paul Newcome · 12/20/18

24 hour time DEFINITELY makes it easier, but I have found that using an extra helper column (or two or three depending on how short you want to keep your formulas) makes the conversion of 12 hour to 24 hour actually pretty simple. Something along the lines of

=IF(FIND("PM", [Time Column]@row) > 0, (VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + 12) + "" + RIGHT([Time Column]@row, 2), LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + "" + RIGHT([Time Column]@row, 2))

This does the conversion all in one. I usually break the hours and minutes out separately, do the conversion and then do the calculations from there. It makes it easier (in my opinion) further down the chain when you are doing the calculations to take into account if the minutes add up to more than 60 (plus one to the hours) and all that mess.

The reason I asked about the lunch break is because factoring the same number every time is easier than having to run yet another calculation (although it only becomes an additional helper column or two).

JulianaD · 12/20/18

Lunch is always 30 minutes!

The total hour calculation is currently Endtime-Startime - (lunchend-lunchstart).

Paul Newcome · 12/26/18

And will anybody be working through midnight into the next day?

JulianaD · 01/04/19

It is rare but it does happen sometimes, does that greatly complicate the formula?

Paul Newcome · 01/04/19

It could, or...

You could have two lines entered, one for each day.

Monday: 10:30 PM - 12:00 AM

Tuesday: 12:00 AM - 6:30 AM

Would that be an option?

Calculating total hours worked in a shift

Comments

Help Article Resources

Categories