Hi,

Yes, but it's a fairly extensive and complex solution. Can you tell me more about your process? How do you handle it today?

Edit: Maybe I misunderstood. Do you only want to collect the information? No need to sum everything?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Here is a thorough list of Smartsheet's functions. https://help.smartsheet.com/functions

There is very little there regarding time explicitly. And though Google Sheets and Excel can manage time calculations and displaying data as durations and works with time efficiently, Smartsheets for some reason, does not. 

I have a few different set ups. One thing I have found though is using multiple helper columns to spread everything out really helps keep the formulas "relatively" simple.

If you convert everything into the smallest increment necessary (hours --> minutes, minutes --> seconds) BEFORE calculating, that too makes things easier.

If you are just looking for #.## hours to be multiplied to pay rate, that actually simplifies everything further.

Keep in mind. If using a form, you cannot update a row, simply create a new one. You can have multiple forms set up (clock in, clock out for break, clock in from break, leave) and have people select their name fro a dropdown (to avoid Michael from clocking in as Mike and clocking out as Mikey). Then add a COLLECT function into each of your formulas to pull all of Michael's rows for that particular day.

Or you could have each person submit a single form at the end of the day where they input start and stop times for each of their breaks, etc. (much easier for YOU).

From there you would pull your times. I find it is easier (for me personally at least) to convert the hours to a 24 hour format before calculation. Then you would calculate your hours difference, calculate your minutes difference, divide the minutes by 60 to give you a decimal, add the end results for the hours and minutes together and now you have your hours worked for that day.

As everyone will agree... It can get rather complicated, but if you spread it out with a column for each step, while you may end up with a lot of columns, you won't have any formulas that are wicked stupid and convoluted.

I have set up a sheet that will pull the time from the created time stamp, compare it to the time in the last modified time stamp, find the difference, and display the result as "## Days ## Hours ## Minutes ## Seconds".

Your solution is actually relatively "simple" by comparison.

If you need more details, feel free to share a few screenshots and details, and we'll see if we can't figure something out for you.

If all that they are doing is putting in that they worked x amount of hours that day, I would suggest Andree's idea. Each person having their own sheet then using a sort of master sheet or rollup sheet. 

Maybe use a form for them to enter their hours to avoid anyone accidentally putting it in the wrong cell. Then use a simple SUM function. But then again... If you are going to create a form for entry, you wouldn't need a sheet for each person. Create a form for person A called "Person A". Have their name as the default value in a "hidden field" on the form so it automatically populates on the master sheet. Then you "clone" the form and just change the name to "Person B" along with the hidden field's default value., Clone and update for Person C, so on and so forth and send each person the URL for their personal form. All of that just helps make sure the right person is putting the right amount of time in the right field.

From there you could use a rollup sheet where each person's name is only listed once and total the values there.

That's definitely a good route to go. I just got a little carried away. I had to put together a "SS Presentation" for some of the VP's here. I had to include a little bit of everything that SS can do and add in a bunch of automation and linking and all that other fancy mess. I was still in overkill mode. Lol. 

Andree,

Thanks for keeping it simple. Lol