Time Sheets

rkrahn

Hello,

Is there any way to create a time sheet in Smart Sheet that will keep track of when an employee submits the time sheet. This way people are not having to print out a time sheet, sign it and turn in a piece of paper? Everything would be able to be done electronically.

Andrée Starå

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

Mike Wilday

I second Andree. Smartsheets does not do a good job of calculating time. There are many reported issues with time calculation, display. Smartsheets works best when focusing on tasks that are day long tasks and beyond. 

You could set up a form to request time card submissions. which would capture your users submissions and timestamp them. But if you are counting on Smartsheets to do good time-calculations... I would suggest finding another solution. 

rkrahn

I would like to be able to capture the users submissions and keep track of their hours for the pay period.

Andrée Starå

I've set this up previously but if it will work for your use case depends.

Can you share something that you use to track it today?

Best,

Andrée

Mike Wilday

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. 

 

rkrahn

We track everything in our department by hand. I want to modernize it a bit. I have attached a sample of the time sheets we use. I hope this helps.

Paul Newcome

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.

 

 

Andrée Starå

Thanks! There are a few ways to set it up.

1. Everyone could have their own sheet
2. Everything could be collected on one sheet, and you could use filters or reports to sort the information

Would that work?

Paul Newcome

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.

rkrahn

That is what I am looking to do, everyone to have there own time sheet and then pull reports to filter information. I think that would be the best.

Paul Newcome

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

Andrée Starå

Overkill mode 

rkrahn

Yes, this would work perfectly. What is the best way to go about setting this up.

Andrée Starå

Which one?

rkrahn

Sorry, is there a way to have a time sheet for each person and also pull a report of certain information from the individual time sheets?