Excel time sheet with 4 pay rates during same day


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. 



For this the layout of your worksheet is critical. Please post it. Best, attach it to your original question.
Variatus (rep: 2938) Feb 19, '18 at 7:41 pm
I added some stuff to my previous post. Im having trouble attaching the numbers file to it so you can see what Im working with. If you need more help or info let me know how I can get a screenshot to you so you can visualize the problem Im looking at.
Tman1078 (rep: 2) Feb 20, '18 at 8:41 am
Ok I finally got one to upload 
Tman1078 (rep: 2) Feb 21, '18 at 1:47 pm
I've been trying to download your workbook all day without success. Likely, it's just a weak connection I have here but so persistent, that's unusual. I shall try again tomorrow. Meanwhile, please download the workbook from your end. If it gives any trouble, delete and upload again.
Variatus (rep: 2938) Feb 22, '18 at 5:53 am
Its working on my end. I deleted the original mock-up from my files and downloaded it here with success. I even opened it on my phone and it converted to Google sheets and apple numbers on iPhone. I dont know what the trouble could be. All of my stuff is updated and I dont know if it would matter what version you have or not. I created it on Google sheets and let the program convert it to.xlsx format so it could be shared. 
Tman1078 (rep: 2) Feb 22, '18 at 7:34 am
Add to Discussion


Selected Answer

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.



Thank you so much Variatus. This was way above my head and I will likely obtain a PhD in excel merely from studying your work and implementing it into my full workbook. This will be a challenge that Im guaranteed to learn from. 
Tman1078 (rep: 2) Feb 23, '18 at 10:48 am
I'm glad I could help you out.
Variatus (rep: 2938) Feb 23, '18 at 8:30 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login