Payroll - Daily & Weekly Overtime

[wbeverett]

(This is Sooo Bad, I had all this typed and lost it in the preview)
(This time I "ctrl+c" everything before preview)
(In preview my pic didn't show, I used the wrong link: "Use the second one")

I am creating a weekly expense report that includes pay

Daily Hours <= 8 $10.00
Daily Hours > 8 $15.00
Weekly Hours > 40 $15.00
All hours count toward weekly total
Saturday, Sunday treated like any other day
Work week starts on Sunday

Let's say I work 15 hours a day

Sunday
8 hours @ $10.00 ea.
7 hours @ $15.00 ea.

Monday
8 hours @ $10.00 ea.
7 hours @ $15.00 ea.

Tuesday
8 hours @ $10.00 ea.
7 hours @ $15.00 ea. - over 8 for the day and reached 40/wk

Wednesday (and rest of the week)
0 hours @ $10.00 ea.
15 hours @ $15.00 ea. - over 40/wk

I appreciate your comments!

 

[wbeverett]

I need to modify the alternative formula so that the >40/wk only includes the first 8 of each day starting on Sunday. Thanks!

 

[DonkeyOte]

I can't help but think you would be better off both in terms of simplicity/transparency to break out the daily hours by type, eg:

Excel Workbook
	B	C	D	E	F	G	H	I	J
11		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
12	Description	24-Oct	25-Oct	26-Oct	27-Oct	28-Oct	29-Oct	30-Oct	Total
13	Hours Worked	15	12	10	7	15	4	15	78
14	Regular	8	8	8	7	8	1	0
15	OT	7	4	2	0	7	3	15
16	Hours x Hourly Rate	185	140	110	70	185	55	225	970
17	old approach	185	140	110	90	225	60	225	1035
Sheet1

 

[wbeverett]

Thank you for your response but I would really like to maintain the 2-line format. I went back to using the streamlined version and replaced:

=IF(SUM($C$13:C$13)<40,

with:

=IF(SUM(MIN(8,$C$13),MIN(8,C$13))-MIN(8,C$13)<40,



The problem is that as I carry it across the cells it leaves out the in between cells, eg:

=IF(SUM(MIN(8,$C$13),MIN(8,I$13))-MIN(8,I$13)<40,




With this formula I have to modify each days formula (4th day example):

=IF(SUM(MIN(8,$C$13),MIN(8,$D$13),MIN(8,$E$13),MIN(8,F$13))-MIN(8,F$13)<40,


and having to do that I simplify to:

=IF(SUM(MIN(8,$C$13),MIN(8,$D$13),MIN(8,$E$13))<40,




I really just wanted:

=IF(SUM(MIN(8,$C$13):MIN(8,C$13))-MIN(8,C$13)<40,


but of'course that doesn't work. Other issues pop up, the formula needs to know to start calculating overtime on hours when the 40 is met:

1st 2nd 3rd 4th 5th 6th 7th

_9__7__12__8__10__5__6
__________________^
__________________1 hour reg 4 hours ot



I have spent alot of time on this, more-so than I should have, so if anyone can help I'll be most grateful.

Thanks in Advance.

 

[DonkeyOte]

The 2 line format may seem more elegant but it's equally less efficient.

=SUM(MIN(8,C$13,MAX(0,40-SUM(IF(ISNUMBER($B$13:B$13)*($B$13:B$13>8),8,$B$13:B13))))*10,(C$13-MIN(8,C$13,MAX(0,40-SUM(IF(ISNUMBER($B$13:B$13)*($B$13:B$13>8),8,$B$13:B13)))))*15)
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)

Other alternatives would include use of SUBTOTAL & OFFSET but at that point the formula becomes Volatile which given it's inefficiency is far from ideal.

 

[wbeverett]

This works exactly as I had requested.
You obviously Excel in Excel
Thank you for your time.