Consolidate time sheets

Hugh McKinney

Hi relative newbie here... looking for help. I would like to set up time sheets for individual team members is a design studio. They will list jobs worked on by job number to allocate time worked on daily. I would then like to consolidate all the data into one master sheet and run a SUM IF formula to calculate the total amount of time spent on each job and allocate the time calculated to each job number. I need help in finding a way to consolidate each time sheet into a master sheet to run the SUM IF formula. Hope this makes sense. Thanks in advance

Adam Overton

You could go to their sheet, select the rows, and choose Move to Another sheet, but it still feels very manual.

 

What about you make your master sheet where it contains a drop down field where you list each job, the hours worked, by whom the work was done, the date, or whatever else you need.  Next, you create a web form by clicking on the the Web Forms tab at the bottom, and choosing create new form. Save the web form and preview it so you can see what this will do. Copy the link and send it to your team members. When they do work, they can log the time they do using the web form. It will automatically be added to your master sheet. You can do your calculations there, and you don't need individual sheets. If you want, you can create a report that filters it down to individual team members to see what their timesheet would be.

 

For added convenience, you could copy the web form and make a version for each team member, where their name and favorite project is set as defaults for those in the web form.

 

Hugh McKinney

Thanks Adam, I have looked at a number of solutions including web forms. Team members will be logging time for multiple jobs per day so a sheet format will work much better, web forms would work if we were looging time per job which would be time consuming.

 

To see if I can resolve this I am looking at putting a master time sheet together with a drop menu for team members, then add a filter per user and  publish the sheet to each team members. 

 

Thanks for you comments much appriceated.

H

Hugh McKinney

By Gingo I think I've cracked it. 

 

Create a master "time sheet" and add a column with a drop menu listing all team members.

Add named filters for each of the team member.

Invite each team member as a shared user to the sheet (Editor - cannot share)

Before copying the "Secure sheet link" click the edit button beside "Default View"

In the "Filter" drop down select the team member you are sending that specific link to.

In the "View" drop down select "Grid View" or your prefered view.

Hit "Save"

Copy the secure link and email to the selected team member

Repeat the process for all team members.

Each team member will have a default view with their filter active.

 

Ideally it would be great if each named filter could be locked or the filter dropdown bar could be hidden to ensure the user does not change the named filter in error. 

 

This will now give me the ability to calculate total time spent on each project by running a SUMIF formula by job number and link the total amount to the master production schedule.... Marvellous!

 

Adam Overton

I agree with Hugh, and I recommend instead of creating a filter for each user, you can create a filter for all in one by choosing "Current User" in the appropriate field.