Solved

Excel VBA Running total by person then calculate overtime

Posted on 2013-01-07
23
676 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
Comment Utility
Hi can you send your spreadsheet, it was not attached
0
 
LVL 4

Expert Comment

by:Jorgen
Comment Utility
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
Comment Utility
I have tried to attach the spreadsheet again.
OvertimeCalculation.xls
0
 

Author Comment

by:minamina6
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:minamina6
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Jorgen,
I don't see any attachments.
Beth
0
 
LVL 4

Expert Comment

by:Jorgen
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now