Calculating total hours worked in a shift
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.
Comments

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?

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.

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).

Lunch is always 30 minutes!
The total hour calculation is currently EndtimeStartime  (lunchendlunchstart).

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

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

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?
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 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!