Solved

Excel VBA Running total by person then calculate overtime

Posted on 2013-01-07
23
722 Views
Last Modified: 2013-01-31
I need to calculate overtime (hours over 40) by project and I think I need a running total and a loop to do it.  The data is only given to me by person by daily hours by project. I need to split it into straight time (<=40) and overtime (>40).  I need it split that way before I can calculate total costs (ST hours * ST Rate) + (OT hours * OT Rate).

I have attached a copy of my spreadsheet. Subtotals don't really help because the calculations need to happen as the loop continues.

For Employee A, I need to keep a running total of their hours. When their hours become greater than 40, I need to start populating the overtime column.

Since I'm better at showing than telling, I have put a visual example of what I'm looking for in my attachment.
0
Comment
Question by:minamina6
  • 11
  • 11
23 Comments
 
LVL 16

Expert Comment

by:terencino
ID: 38753041
Hi can you send your spreadsheet, it was not attached
0
 
LVL 4

Expert Comment

by:Jorgen
ID: 38754435
It might be obvious in your spreadsheet, when that is send, but normally I experience a problem counting the way I understand your description.

At least here in Denmark the overtime is decided by the total hours for the week, and secondly that cost can be allocated to a given project.

From your description I understand, that you want to calculate overtime per person and project, which means that if you have an employee working 30 hours on 3 projects could in total have 90 hours and no overtime, while another employee could have 42 hours on one project, and thereby have 2 overtime hours.

I expect that you want all hours larger than 40 hours in a given week to count as overtime, and then make your calculation of overtime.

Before suggesting any solution, it will be nice to know.

regards

Jørgen
0
 

Author Comment

by:minamina6
ID: 38754567
I have tried to attach the spreadsheet again.
OvertimeCalculation.xls
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:minamina6
ID: 38754594
Overtime is any time over 40 hours for the week.  

I can see from Jorgen's comment that I haven't explained things well enough. Jorgen said "if you have an employee working 30 hours on 3 projects could in total have 90 hours and no overtime." This is not correct.

If I have 1 employee working on 30 hours on 3 projects, the total straight time is 30 hours. Or if someone did work 90 hours that week on 3 different projects, the overtime would be 50 hours.

Overtime is not split evenly over projects. It depends on the order of projects on the data sheet. I know, its odd but that's the way my client keeps data.
0
 
LVL 4

Expert Comment

by:Jorgen
ID: 38754717
Hi Again,

So it will always be the bottom project (or more specific the project you reach first starting with the topline), that need to be split between standardtime and overtime.

Do you need to create the daily schedule, or can you use the rawdata to generate your running totals?.
 
The way you have data makes it a little difficult to make running totals.

regards

Jørgen
0
 

Author Comment

by:minamina6
ID: 38754889
Jørgen ,
I don't need to create a daily schedule. The raw data can be used to generate the running total.
Yes, the project you reach first starting with the top line gets overtime.

Maybe I should try this
1)  Create a subtotal row by person by day
2)  Create a running total row using the above subtotals
3)  Create formulas in the OT colums that subtract the hours by project from the running total for the day.
4)  Create formulas in the ST columns that leaves only the remaining ST hours.
0
 

Author Comment

by:minamina6
ID: 38754904
Or would it just be easier to pull the data into Access, index it and reformat it before trying to figure out the OT?
0
 
LVL 4

Expert Comment

by:Jorgen
ID: 38757861
Hi again,

Sorry for not getting back to you yesterday, but I had to help my son, that should prepare for exam today. In many cases (dependent on how registrations has to be done) I would absolutely choose Access. Access is a true relational database, and this kind of registration can be made perfectly in a form in Access.

But we can also do the looping and running total in VBA, updating the running totals. I tried to play around with a sumproduct formula and an array calculation to solve your problem, and I will give that a try again today.

But if VBA is a possibility, that would be my first Excel choice.

regards

Jørgen
0
 
LVL 4

Expert Comment

by:Jorgen
ID: 38764863
Hi Again,

Hoped that you would get back to me regarding the VBA part on this. I would suggest to program some VBA , that would loop through all records on the monday register a person in a dynamic multidimensional array, in the second dimension, I would suggest that you sum the number of hours so far. Then in the nested loop we would put in an if statement that measures if the total for the person is larger or lower than 40. If larger than 40 then the value of the measured cell should be reduced to the difference between 40 and the last aggregated sum, and the cell to the right for the measured cell should have the new summed value -40. If we have not reached 40 yet, just sum and go to the next row

But if VBA is not an option then I will not go down that row.

regards

Jørgen
0
 

Author Comment

by:minamina6
ID: 38764884
Yes, VBA is an option. I have done some VBA but I'm not very good setting up loops. And a multidimensional array is way out of my sphere of knowledge.
0
 
LVL 4

Expert Comment

by:Jorgen
ID: 38764935
Multidimensional arrays is not what I have been doing too much of either :-) But I now the principel, and have a dynamic array working and inserting to a sheet. So I will play around with the multidimensional array to see if we can make it work.

I have also been looking at the the sumif formula, but they do get extremely long and messy.

Just had a second thought on a VBA possibility

The first sheet is as I understand it the raw data. Is that a fixed input sheet, or could your remove the overtime from that sheet and then have a second sheet generated with both standard time and overtime?

regards

Jørgen
0
 

Author Comment

by:minamina6
ID: 38774269
Jorgen,
Somehow my response last week didn't post. Sorry for the delay. On the raw data sheet, I add in the overtime columns. I was figuring out overtime manually so I added columns.
So yes, I could start with a worksheet without overtime columns and generate a second sheet with standard time and over time.
0
 
LVL 4

Expert Comment

by:Jorgen
ID: 38775992
OK, I hoped that you would respond a little before, so I could have looked in the weekend. But I will have a go for it tomorrow (it is quite late here in Denmark now).

regards

Jørgen
0
 

Author Comment

by:minamina6
ID: 38776050
Denmark?!? How did we ever find answers to anything before the internet came along?

Beth (who is currently working in Burlington, Kansas, approximately 7524 km or 4676 miles away)
0
 
LVL 4

Expert Comment

by:Jorgen
ID: 38780317
Hi Beth,

Yes life is fun, and the world has suddenly become smaller.

I have attached a file (not yet finalized). But try to see if something like the macro AggregateHours could  be something useful and then look at the macro CreateKeys to see how I would generate the list of unique records.

If you agree I will try to combine the two files.

regards

Jørgen
0
 

Author Comment

by:minamina6
ID: 38780372
Jorgen,
I don't see any attachments.
Beth
0
 
LVL 4

Expert Comment

by:Jorgen
ID: 38784096
Hi Beth,

Sorry about that. This time I also pushed the attached button

The aggregate function runs through each person and counts hers or his hours. If you pass 40 it will put the hours in overtime.

The create keys function does in this macro generate a list of names, but that can be updated to a unique key that combines employee and project.

I Imagine that the create key can generate the unique keys, and then within that macro I will run the calculation of time per person.

Jørgen
Overtime.xlsm
0
 

Author Comment

by:minamina6
ID: 38789046
Jorgen,
I am not able to open the file. Could you save it in an earlier version of Excel like 2000 or 2003?
Beth
0
 
LVL 4

Expert Comment

by:Jorgen
ID: 38791597
Hi Beth,

I will just read through the code to be sure, that it works in 2003 version as well. I believe it does, but - I will check and send to you.

regards

Jørgen
0
 

Author Comment

by:minamina6
ID: 38812769
Jorgen,
Would you please send it in a lower version? I think the problem is that the antiquated software here does not recognize .xlsm format, but .xls format should be ok.
Beth
0
 
LVL 4

Accepted Solution

by:
Jorgen earned 500 total points
ID: 38815773
Beth

Sorry about the delay - I got a fever. And have not been to well since last weekend.

I hope that the conversion worked well.

regards

Jørgen
Overtime1.xls
0
 

Author Comment

by:minamina6
ID: 38836303
Jorgen,
Thank you for changing the file format. i was able to open it. Your answer isn't really what I was looking for. I think my issue is too complicated to do in Excel. This morning, I pulled the data into Access and did the calculations there.
Thanks anyway,
Beth
0
 
LVL 4

Expert Comment

by:Jorgen
ID: 38840936
Hi Beth

Access is often a better solution than Excel for this kind of work. I believe that we could have done the last bit in Excel as well. I might play with your file and send you an update (if I get a little more time). But I must admit, that my choice would have been Access in the first place.

Good luck with the database.

regards

Jørgen
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question