Transferring a timesheet into a datasheet

Afternoon, all

I've been asked to create an excel based timesheet for my project team to complete each week. I want to consolidate the data into a large data sheet, within the timesheet itself, that I can easily copy and paste into a master sheet and then manipulate / pivot as I wish.
I have attached a copy of the timesheet. I have populated the 'datasheet' tab with values, showing what I am hoping to achieve using formula / VB script. I've spent a little while trying to figure this out for myself and am really struggling - I wonder if anyone has any experience of doing something similar?
What I am struggling most with is if, for example, there are multiple entries against a single row (Activity "Kick off and PM Setup" is populated in columns E, G and I to indicate time was worked against this on Monday, Wednesday and Friday), how do I convert that into multiple rows on my datasheet? Maybe it's not possible?

Any advice / help would be appreciated (although I am aware there are hundreds of timesheeting systems out there )!

Many thanks
Tom

Why do you need timesheet?
Why is not there any weekdays in datasheet?

Hi Tim

Thanks for your reply.

Why do you need timesheet?
If you're asking generally - I need a the project team to complete timesheets so I can identify the effort and associated cost spent on different project phases and activities.
If you're asking why I can't just use the datasheet - The idea is that the datasheet is hidden and my team can only see a locked down version of the timesheet. I want to keep the timesheet simple to complete so it can't be messed up. I wouldn't expect/trust the team to accurately complete the datasheet themselves. But I need the data in a format similar to that in the datasheet to be able to consolidate and pivot etc.

Why is not there any weekdays in datasheet?
I didn't think it was necessary, given that there is a 'date' column.

Thanks!
Tom

datasheet: expected/planned hours?
timesheet: hours are input by team members (it does not mean "done")?
in this case data layout should be the same to compare

No, sorry - I don't think I've explained myself very clearly..

The timesheet is to be completed at the end of each week by multiple users, showing what they have worked on during the week.

I want to convert the timesheet into the datasheet format (I'm guessing this would have to be using code...). But, as per the example data that I have populated in the datasheet, I want a separate row per timesheet hour entry (there are 11 different hour entries on the timesheet, but split across only 6 rows. These need to convert into 11 separate rows on the datasheet...

That make sense?

Cheers

it can be done with formulas in an additional sheet end then coping values from it