Time Sheet Problems

[nbell]

We use a time clock system that generates a daily Excel report, an example shown here:
I'm looking for help with this time sheet data development.

The following is a sample of what is loaded into my computer each day from our time clock. The column headings, "Recording Unit", "Registered Time", "Activity", "Button Code" and "Employee" are created by the machine as an excel file and are self explanatory. The two column headings, "Hours" and "Total" are added by me and are what I am looking for a solution to.

Recording Unit - Registered Time - Activity - Button Code － Employee － Hours － Total
Time Clock | 2011-12-16 06:52 | Start | 00251905 | ChangShun | ? | ?
Time Clock | 2011-12-17 19:06 | Stop | 00251905 | ChangShun | ? | ?
Time Clock | 2011-12-15 06:56 | Start | 00253070 | Chen Hai Li | ? | ?
Time Clock | 2011-12-16 07:03 | Stop | 00253070 | Chen Hai Li | ? | ?
Time Clock | 2011-12-15 18:56 | Start | 00253070 | Ma Hai Tao | ? | ?
Time Clock | 2011-12-16 07:05 | Stop | 00253070 | Ma Hai Tao | ? | ?
(Sorry that the "Column Headings" don't line up with the data cells.... )

There are actually "start/stop" records for twenty employees similar to the above for each day. Each day the new records are appended to the bottom of the spread sheet so by the end of a 30 day month there are 1,200 records (20 employees X 2 records per day X 30 days)

So each day the main worksheet needs to be sorted by (1)Employee and (2)Date/Time. Then the hours calculation needs to be done based on one of the three employee categories - Problem 1. (Problem 1: If you look at the start and stop times, you will see we have three types of employees. One type works 24 hours then off 24 hours so their time overlaps two days. Another works either 12 hours per day but starts in the evening of one day so his time also overlaps into the next day. The third type works 12 hours and her time is all in one day. To add to the confusion, the 24hr on and 24hr off employees DO NOT get a day off but the 12 hr per day employee's do. We have twenty employees that fall into one of these three categories.) (By the way... we are not mean... the 24hr employees get to sleep at night and are provided sleeping quarters. The 12 hr employees get a mid day break of about three hours as well.)

At the beginning of each month I want the workbook to use the list of employees (can be provided in another workbook or worksheet... doesn't matter) to create a separate worksheet (data to come from main worksheet #1) for each employee and the final calculations for each employee, each day, should be shown on their individual worksheet to meet the following objectives.

Objective One: To calculate hours worked each day (in column "Hours") and total hours worked in month (in column "Total")
Objective Two: To create a separate worksheet for each employee and update it daily with information taken from the main worksheet #1 which is also updated daily with the time clock data.

It seems to me since there are three types of employees, we should give each employee and identifying number matching his/her "type" to be able to 'call out' a specific formula to calculate that employee's time.

I sure hope someone can help me with this. I have been struggling with this for about three months now and still have to do all the calculations each day by hand.

Thank You so much in advance!

[Don]

Hi and welcome to the forum!

To calculate the hours worked, you could use a simple formula like this:

Code: Select all

=(A2-A1)*24

Assuming the start time is in cell A1 and the stop time in cell A2. Just adjust the cell references as needed and make sure the format of the cell is set to General.

Once you have the hours, you should put this into a PivotTable and see if that will arrange the data as needed.

[nbell]

Hi and Thank You for your response.

You are right.. That part is relatively simple... the problem comes in when the next employee does not have the same 24 hour work schedule..

Actually, there are three different formula's needed to cover each type of employee and a way to identify each employee each time as to whether he/she is type 1, type 2 or type 3 and then have the appropriate formula for calculating the hours do it's job. Subsequent to that I want the data derived from that formula to be copied to the next row on the appropriate worksheet assigned to that single employee.

I can easily assign a category number to each employee (i.e. Type 1, Type 2, Type 3) to better enable the spreadsheet to determine which formula to use for which employee. Make sense?

I guess this is pretty confusing.... I do apologize!

Thank You again for your help.