Here's a challenge for you! We have 4 different pay scales at work (billable, non-billable, billable o.t., and non-billable o.t.). I have a 7 day "daily hours sheet" in excel and need to have running tally of these 4 values per day inclusive of when they swap over into overtime hours. E.g. an employee might work 7 billable hours and have 3 non-billable hours on monday, tuesday 2 billable hours and 9 non-billable hours, etc. once 40 hours is reached of these 2 combined values, they go into the next two pay scales (billable o.t., non-billable o.t.). I need a formula to show these 4 values and reflect accurately when they swap over into the two overtime values. The way I presently have it is wrong and tallys the billable hours up to 40 hours (of the week total) and counts everything after that whether billable or not as billable overtime and same for non billable hours. With the combination of "base" billable and non billable hours, a person might reach their 40 hours on say Thursday, and I don't have a way of adjusting hours for the remainder of the week whether billable or non billable into the respective overtime scale for those hours(billable o.t. or non-billable). I humbly request the help of minds greater than mine.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Excel time sheet with 4 pay rates during same day
Discussion
Answers
I finally managed to download the file but you may not like what I must tell you: I have been unable to solve your problem using worksheet functions. The attached workbook contains a solution using VBA. It is therefore in xlsm format.
There are two worksheets in your workbook now. I called them "UDF" and "Change Events" after the two systems by which they work. For better understanding, take a look at the code module 'PayCalculation'. (Press Alt+F11 to access the VB Editor.) There you will find four functions called "RegBillable", "OTBillable", "RegNonBillable" and "OTNonBillable". You will find these functions called from the cells UDF!B11:C12. Please take note of the syntax.
Each of these functions calls the function "BillableHours" which calculates all four values. So, each of the UDFs lets "BillableHours" calculate all four values but returns only one of them.
Now, in the VB Editor's Project Explorer window, double-click on the 'Sheet1 (Change Events)' object. That opens the code sheet behind the "Change Events" tab. The procedure you see there also calls the function "Billable Hours" but then proceeds to set all four values. This procedure runs every time you make a change in B2:C8. Therefore the totals are always up to-date.
The UDFs are supposed to work the same way, but they don't - always. To be sure that they are up to-date you should press "Calculate now" on the ribbon's 'Formulas' tab. I can't tell you when this will be necessary, if ever. Best, you try it out.
The UDFs are more versatile. You can use them like normal Excel functions. The event procedure is smarter but more difficult to deploy. I presume that you have many employees and the event procedure will have to be taught to find both the input and the output ranges for each of them. The UDFs have no such problem. They are ready for use as they are.