Team Member Allocation/Timesheet

happinessisland

Hi! I'm trying to create a sheet that will be updated on a weekly basis by Team Members using the form for the sheet:

Which would then show up here:

So here's how our company works: Our TMs work on multiple different projects in a week. Ideally, we would like them to fill out the form Monday morning each week so we can get a rough idea of where they are spending their time. So for instance, maybe Sarah knows that project A123456 has a lot of deadlines that week and knows that will be her main priority, so she can say how many hours roughly each day she expects to be working on this.

What our goals are: To see the sum of work for each person. I know we can do a SUM formula, but as you can see, an employee will most likely fill out the form multiple times to show where their time is being allocated to. Our other goal with this is to see if they are being under or overworked. Maybe Sarah has a lot on her plate but John Smith barely has anything to do. We want to see that and fix that, so this will need to be a sheet that is updated weekly.

What I'm looking for assistance on: With the multiple entries and this being an ongoing report, is there a simple way to have a sum of hours for each employee that can be updated weekly? Can that be done in a report perhaps? Also, in general, is there a better way to go about what I want? My team has done the trial for Resource Management and have decided to pass on the add-on for now, but we still want a way to at least temporarily track this information until we have a proper resource management tool.

Any suggestions at all are greatly appreciated!!

JamesB

@happinessisland Forms unfortunately will always add a row and not update a current row. I would create a sheet for each resource and give them edit rights to their sheet and have them fill out the needed columns. You do not need them to enter their employee name since you know who each sheet belongs to, you can default this information as new rows are added. I would recommend a start of week date column, which you could also automate the assignment of.

Then you can collect all of these sheets into one report for review of all the resources. Or you could use automations or formulas to gather this data onto a master sheet where you can do additional formulas if needed.