Copying time sheet data

[robinh]

Hi,

I have a spreadsheet (in excel 2003) containing a years worth of timesheet data, arranged into columns which include job number, stage and the days of the week for Monday-Friday. Hours per day are put against a project and/or stage. The spreadsheet is arranged into worksheets with two weeks worth of timesheets per excel worksheet.

What I am trying to do is copy the job number, stage and what hours were done on that job number and stage for every day of the week for 2013 into another sheet. This has to have the columns job number, stage, date and number of hours against each job and stage for that date. It looked simple at first but I am having real problems! Maybe I am just being stupid, but has anybody got any suggestions for formula I could use to help me with this?

Any help would be much appreciated!

 

[healey21]

Welcome to the forum,

It sounds as though you would need some VBA code or possibly look at a Pivot table.

Can you copy some example how its laid out into your thread.

 

[BGY23]

Hi,
Assuming your data has a srandard format and a column for hours spent on each day on the project/stage and not just start and end taime per day then you should try a pivot table.

Set the pivot table style to "classic" and format the hours in [h]:mm:ss.

I created a sample and it worked fine.

 

[robinh]

Welcome to the forum,
It sounds as though you would need some VBA code or possibly look at a Pivot table.
Can you copy some example how its laid out into your thread.

Hi Healey,

Thanks for getting back. I can't post attachments yet, so I will copy and paste below. I have also uploaded as a spreadsheet at https://drive.google.com/file/d/0B2HwrFF20fy4S1hFNmtub1NoaG8/edit?usp=sharing

Below is an example of the timesheets in their current format. There are two per worksheet.


[TABLE="width: 1024"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Week Commencing: Monday</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]20.05.13</SPAN>
[/TD]
[TD="colspan: 2"]Week No. Odd</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]21</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Contract Name</SPAN>
[/TD]
[TD]Job No</SPAN>
[/TD]
[TD]Stage</SPAN>
[/TD]
[TD]Overtime</SPAN>
[/TD]
[TD]Normal Hours</SPAN>
[/TD]
[TD]Mon</SPAN>
[/TD]
[TD]O/T</SPAN>
[/TD]
[TD]Tue</SPAN>
[/TD]
[TD]O/T</SPAN>
[/TD]
[TD]Wed</SPAN>
[/TD]
[TD]O/T</SPAN>
[/TD]
[TD]Thu</SPAN>
[/TD]
[TD]O/T</SPAN>
[/TD]
[TD]Fri</SPAN>
[/TD]
[TD]O/T</SPAN>
[/TD]
[TD]W/E</SPAN>
[/TD]
[/TR]
[TR]
[TD]Project A</SPAN>
[/TD]
[TD]4769</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project B</SPAN>
[/TD]
[TD]4644</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]14.5</SPAN>
[/TD]
[TD]7.5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3.5</SPAN>
[/TD]
[TD][/TD]
[TD]3.5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project C</SPAN>
[/TD]
[TD]4855</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]11.5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7.5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Below is the format I have to have the data in (its for an upload template). Only Job number, stage, date and normal hours have to be copied across from the original timesheets.

[TABLE="width: 544"]
<TBODY>[TR]
[TD]Job No</SPAN>
[/TD]
[TD]Stage</SPAN>
[/TD]
[TD]User</SPAN>
[/TD]
[TD]Date</SPAN>
[/TD]
[TD]Normal hours</SPAN>
[/TD]
[TD]Extra Time</SPAN>
[/TD]
[TD]Notes</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


Cheers,
Robin