Setting up a "smart" count for a column for overtime

[loganak]

I am trying to set up a totaling function for regular and overtime in a column. Here is the catch: Over time is met at 8 hours a day AND whenever the employee hits 40 hours in a week. I can run a formula that captures one or the other, but not both. Example of data:

        EE1         EE2           EE3
        16.          9.5.           6
        18.          9.5.            8.5
        17.          10.             9
        15.           9.5.           8
        14.           6.              9 
total:80.           44.5.         40.5
Reg: 22.           33.5.         38
OT: 58.             11.            2.5

Where these are the correct totals. I just cant seem to format any sort of formula that switches process when the running total hits 40 and carries over to OT. Any help is much appreciated!!!           

[#2]

@loganak 

Over Time

Hope I was able to help you with this link.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

The vertical columns are all time entered in one 24hour day, and already set up. I actually have a very similar formula for finding those numbers. Thank you for confirming (and simplifying) I had that set up right.
My Issue is totaling the time for a week, once it hits 40 hours (regardless of if its in 3 days or 5) it is overtime, but ALSO after 8 hours in a day. So often, 3-4 days into the week they are hitting all OT, so less than 40 hours of regular time is accrued. Understand?

[#4]

@loganak Have a look at the attached file. It contains two possible ways to calculate this. Choose the one that works in your Excel version.

[#5]

Sometimes the best answer is a simple one. I think this will work for what i'm trying to achieve.. my first attempts were multiple nested =IF statements combined with MAX mixed in haha. Thank you for the insight!

[#6]

my only caveat; finding that regular time value from a formula. Hand calculating it adds a substantial amount of time to payroll by the number of employees in the company

[#7 · Accepted solution]

@loganak 

Here is a solution that calculates overtime using a custom VBA function. Regular time is calculated as total time - overtime.

[#8]

That is the one thing I haven't spent much time with. I used to be a matlab guy so I need to learn some more in the VBA side of excel. I really appreciate it!