Excel VBA Running total by person then calculate overtime
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.
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.
terencino
Hi can you send your spreadsheet, it was not attached
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
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
ASKER
I have tried to attach the spreadsheet again.
OvertimeCalculation.xls
OvertimeCalculation.xls
ASKER
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.
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.
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
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
ASKER
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.
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.
ASKER
Or would it just be easier to pull the data into Access, index it and reformat it before trying to figure out the OT?
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
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
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
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
ASKER
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.
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
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
ASKER
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.
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.
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
regards
Jørgen
ASKER
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)
Beth (who is currently working in Burlington, Kansas, approximately 7524 km or 4676 miles away)
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
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
ASKER
Jorgen,
I don't see any attachments.
Beth
I don't see any attachments.
Beth
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
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
ASKER
Jorgen,
I am not able to open the file. Could you save it in an earlier version of Excel like 2000 or 2003?
Beth
I am not able to open the file. Could you save it in an earlier version of Excel like 2000 or 2003?
Beth
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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