Analyze and store construction data

[Construction_Mng]

Hello,

I am new to this forum and am looking for a little bit of help with creating a macro.

I work in the construction industry and need to keep accurate records of the activities completed by my crew and how many hours it took them to complete each activity. I must do this on a daily basis and it can be very time consuming when done manually. I need to refine the process and make it as simple as possible.

The information is usually submitted to me on a hand written report and is laid out as follows:


A B C D E F G H I J
1 Date Task 1 Task 1 Task 1 Task 2 Task 2 Task 2 Task 3 Task 3 Task 3 and so on....
2 Employee Name RT OT RT RT OT RT RT OT RT
3 Employee 1 4 4 2
4 Employee 2 3 5 2
5 Employee 3 6 4
and so on...


RT=Regular Time
OT=Overtime Time
DT=Double Time
Task # = (This is usually a numerical code - "cost codes")

I then insert the information into an excel file (laid out the same way as the above) and send it to accounting/payroll for processing.

What I would like to do is create a macro that would store all of this data into a separate worksheet, keeping a running total of all the hours spent on each task, by employee and by date.

The database would be laid out as follows:



A B C D E F
Date Employee Number/Name Task (Cost Code) Regular Time Hours Over time Hours Double time Hours




** Note I would also be adding other columns such as hourly wage, total daily cost, etc.



I would like the macro to count how many combinations of employee/tasks there is for the day and insert that number of rows to the database. For the example I provided, the macro should insert 7 records to the database (2 records for employee 1, 3 records for employee 2, 2 records for employee 3).

Note that the # of tasks could vary from day to day, and the same goes for the number of employees.

I've tried searching through Google & YouTube but I could not seem to find any examples similar to this. I hope I provided enough information for someone to help me this.

 

[Construction_Mng]

***Correction***

The tables were supposed to look like this:


A B C D E F G H I J
1 Date Task 1 Task 1 Task 1 Task 2 Task 2 Task 2 Task 3 Task 3 Task 3 and so on....
2 Employee Name RT OT DT RT OT DT RT OT DT
3 Employee 1 4 4 2
4 Employee 2 3 5 2
5 Employee 3 6 4

and so on...

----------------------------------------------------------------------------------------------------------------------------------------------------



A B C D E F
Date Employee Number/Name Task (Cost Code) Regular Time Hours Over time Hours Double time Hours

 

[Construction_Mng]

Lets try this another time... Tables are supposed to be laid out as follows:


_______ A__________________ B ________________C ___________________D________________ E________________ F________________ G________________ H________________ I________________ J
1 ____ Date _______________Task 1____________ Task 1____________ Task 1 ____________ Task 2 ____________ Task 2 ____________ Task 2 ____________ Task 3 ____________ Task 3 ____________ Task 3____________ and so on....
2 Employee Name__________ RT ________________OT________________ DT________________ RT ________________OT________________ DT________________ RT ________________OT________________ DT
3 Employee 1______________ 4_________________ 4________________________________________________________2
4 Employee 2______________ 3________________________________________________________5__________________________________________________________________________2
5 Employee 3_____________________________________________________ 6______________________________________________________ 4




____________________________________________________________________________






_______ A__________________ B ____________________C ___________________D_______________________ E_______________________ F___________
______Date________Employee Number/Name___ Task (Cost Code)_____ Regular Time Hours_________ Over time Hours__________ Double time Hours

 

[BlueHornet]

Hello, and welcome to the forum.

May I respectfully suggest that your problem isn't really what you think it is. That is, you seem to be attempting - with limited knowledge of Excel - to build a scratch time and attendance system to capture hours spent by employee, by pay type (Straight time, Overtime and Double time), by contract / task (or maybe both: Contract, then Task within the contract) and by date, of course.

You can certainly do that; after all, IT HAS BEEN DONE SO MANY TIMES ALREADY... What's one time more? Seriously, if you want to play with Excel, then that's as good a way as any to start to learn it as any. And if you're bound and determined to go that route, then we all can help you travel that road.

But you sound like someone who is trying to actually get work done, meet a payroll, satisfy clients, etc. So my suggestion would be to find a commercial, off-the-shelf, working, debugged and proven time and attendance tracking package and save yourself the headache of developing this from scratch. In the long run it's going to be cheaper and a lot quicker to deploy. And the biggest time-saver of all would be to have your employees, or at least their foremen, do the electronic entry in the first place. (My own experience these days is putting my own time into workforcehosting.com for my employer, but there have to be hundreds of such offerings.)

Once you have the consolidated time from all employees then you'll still have plenty of work to do in Excel using pivot tables to slice-and-dice and otherwise analyze all of the data you'll be getting across the company and all employees (and contracts and foremen, etc.); it's not like I'm suggesting that you give up your job here, if your preferred job is playing with and learning Excel. But if you aren't already a developer, you're sort of asking people here to develop an application for you in their spare time. (Hey, good luck to you if someone wants to re-invent the wheel for you.)

Seriously, though. Welcome to the forum.