Calculating overtime....

I have been working on a time sheet for my company because the one they use is lame.

I can not realy explain what I have thus far accomplished with out showing you what I have come up with thus far so my time sheet is attached.

the format is necessary and my totals always come out right.

what I need are a day to day account for my times. any overtime must be shown in the overtime column and not shown in the standard time column

What I have done to accomplish this so far is create a =MIN and =MAX formula where it calculates that any time exceeding a set amount of hours is calculated and shown else 0 is shown in the column.

an example

the standard time is calculated by this formula

=MIN($M$30;O15)

cell O15 has a formula calculating the actual time worked

=(L15-K15+(L15<K15))*24

and the overtime is calculated by this formula

=MAX(0;O15-$M$30)

cell M30 has a fixed hour in my case "6" I actually work a 10 hour day but have the day separated into two separate columns as I take lunch at the same time of say and if overtime is worked it will be the later part of the day and will be over the 6 hour mark for the second column.

I'm sure none of this made sense so please download my spreadsheet to take a look.


when time is imputed into my spreadsheet it calculates what is standard time and what is overtime and sorts them into the correct columns


This works great however does not also account for a 40 hour work week

so I need help either adding this variable into my current equations or a new one to account for both variables.

just had an idea

I can use this equation to calculate for over 40 hours

=IF(O29>O30;L22-K22;0)

but how do i combine it with my original equation?

=MAX(0;O22-$M$30)

Finally I got it

=IF((O15>O30);(L15-K15)*24;MAX(0;O15-$M$30))

thanks for forcing me to do it on my own. I learned allot of ways how not to do it