PDA

View Full Version : Excel timeclock data vba help



TheronW
04-24-2012, 10:05 AM
Hi everyone I'm having a tough time trying to figure out how to work vba code for a time clock spreadsheet (I've attached a sample of the data). I'm a total noob and way over my head on this project.

thanks in advance for any of your expertise and patience- I hope I'm explaining in detail enough as to what I need. Let me know if I need to clarify anything

I've attached a sample document

First for each employee I need to calculate:

Their earliest start_date col (E)
Their latest end_date col (L)
Their total paid_time (C)
Their total paid_time + [total adj_min_to_start_time (H) + total adj_min_to_end_time (L) 'but don't count any marked as OVERTIME (C)]


Finally I need to have totals for:

earliest start_date (E) out of all the employees
latest end_date (L) out of all employees
total number of shifts of all employees (records)
total number of employees (A)
total paid_time (D)
total paid_time (D) + total adj_min_to_start_time (H) + total adj_min_to_end_time (L)
total difference of [total adj_min_to_start_time (H) + total adj_min_to_end_time (L)] - paid_time (D)

thank you!

Bob Phillips
04-24-2012, 10:42 AM
How about this?

TheronW
04-24-2012, 12:38 PM
Hi. Thank you for the response and the results do look correct; however, how would I replicate this for 700,000 records? I was thinking vba code and some type of iteration through the records, but is there another way?

Sorry if the answer is obvious- I'm totally new to excel programming.

thanks again

Bob Phillips
04-24-2012, 04:28 PM
Replace $25 in the formulae for your last row.

TheronW
04-24-2012, 04:51 PM
Hello and thank you again for your help on my spreadsheet. I hope I don't come across as a total idiot but I have a few questions about what you put together.

1. When you say to change $25 in the formula with the number of rows in my large spreadsheet- where exactly is this formula that I would edit? I looked in the vba code editor and couldn't find any formulas in any modules.

2. When you subtracted the the adjusted time from the paid time (not counting records with OVERTIME) it's not calculating the correct amount. For example:
Acorn, Tod adjusted paid time should be
17.6 (paid time) - (4+3+1) = 9.6 and not 16

thank you again

best regards

Bob Phillips
04-24-2012, 05:04 PM
It is not VBA, it is Excel formulae. On the Summary sheet, there are formula in column A that get the list of names, B and so on that do the calculations. It is these that need changing.

The paid time is a decimal number, which I assumed were hours. The adjustment are time, minutes, and your original post says add not subtract.