Help with timesheet created by MS Forms

[BillZab]

Hi Folks, new to this forum so please be gentle 🙂

I have 8 employees who use a mS Form I have created to input their weekly timesheet. At the end of the week, I save the results to an excel file. All the entries are in random order and each employee can have various amounts of entries. I use a macro to sort the entries into day order (Monday, Tuesday, etc.) and the sort them into employee order alphabetically.  They input hours for each entry and I can total the hours for the whole sheet. This works reasonably well and it produces a PDF that we save for each week. What I would like to do, is be able to insert a row after each employee and total their individual hours for the week within that row. Any help with how to do this would be appreciated.

[#2]

@BillZab 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

@HansVogelaar Please see above. This is what I get from the form after I remove the columns I dont need and sort the order of the day of the week and then the employees. The number of rows can vary from week to week. What I am looking for is a way to add up the hours of each employees. Ideally I would insert a row between each employee the total the hours for each employee. It needs to be done in a macro. TIA.

[#4]

@BillZab 

What do you mean by "Please see above"?

[#5]

Sorry @HansVogelaar I thought I had attached a screenshot. I will try again tomorrow when I have more time.

[#6]

[#7]

Sorry about the problem getting the photo of the sheet to attach.
What I am trying to do, is insert a row between Joe and Dave, then total Joe's hours and so on. I can do this manually easy enough, but I need it done in a macro. The row count will vary from week to week. Again, any help will be appreciated. TIA

[#8 · Accepted solution]

@BillZab 

You can use Excel's Subtotal feature.

Click anywhere in the data.

Activate the Data tab of the ribbon and click Subtotal in the Outline group:

The Subtotal should propose to add a subtotal for each change in the NAME column, and add it to the HOURS column:

Click OK. Result (with some random dummy data):

[#9]

Many thanks @HansVogelaar. That has worked perfectly. I had a few issues as the SubTotal option was grayed out, but found a solution on the web. Thanks again for your help.

[#10]

@BillZab 

Select the HOURS column.

On the Data tab of the ribbon, click Text to Columns, then click Finish without changing any settings.

Does that make Subtotal available?

[#11]

That's it working perfectly Hans. Many thanks.

[#12]

@BillZab 

i am trying to create an MS forms timesheet template for my employees with the ability to export to excel however I’m struggling to even get the form off the ground, is there any chance you have a template you have created that you could share? It would be really appreciated!

Thanks

Matty

[#13]

Hi Matty,

I am not sure how to save the form as a template to enable you to view it. However, I can offer the following:

1. I have created a form that my employees can access. (if you create a form for those with an MS account, then their name and date are collected automatically so no need for those entries in the form).
2. Our form asks 5 simple question. What day of the week is it (using a drop down list), Who are you working for, Where are you working, what were you doing and finally How many hours did it take you. Our user can enter as many entries in a week as required. Some will work all day on the same job, some may work a few hours on one job and a few hours on another, etc.
3. At the end of the week, or when ever it suits you, we then download an excel sheet of all the answers. If everyone has filled it in correctly, we can then delete all the responses to clear for the following week.
4. Now comes the tricky part. Each entry will be in a random order, so we use a macro to sort the entries into days of the week, then each individual. We then insert a line and add each individual hours. We also delete all the columns we don't require and resize the columns so that it prints on a single sheet of paper in landscape mode. The macro then asks for the week no and it is printed in the header. We then end up with a PDF of our employees timesheets for that week which can be filed or printed as required.

The best way to do the macro is record it as you sort the sheet out, then you can go back in and edit it as needed later.

I hope this helps.