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!

 

[DonkeyOte]

With more thought this can be streamlined (significantly)

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	15	15	15	15	15	15	105
14	Hours x Hourly Rate	185.00	185.00	185.00	225.00	225.00	225.00	225.00	1455
Sheet4

 

[DonkeyOte]

Another alternative (row 15)

Excel Workbook
	A	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	8	15	15	15	90
14		Hours x Hourly Rate	185.00	140.00	110.00	105.00	225.00	225.00	225.00	1215.00
15			185.00	140.00	110.00	105.00	225.00	225.00	225.00	1215.00
Sheet4

 

[wbeverett]

Thank you for the response. It will be a little while before I can look at this but will let you know when I can work with it. Thanks!

Sorry for the multiple post.

 

[wbeverett]

This is Great!!! I attempted this on my on several hours before I signed up for MrExcel, including watching some of his you tube videos. I never would have gotten it without your help. Thanks!

 

[rjs1943]

Donkey:

How do you change the hourly rate and overtime rate in in your alternative formula? Say from 10 dollars to 15 dollars an hour and from time and a half to double time. Can I enter them in another cell to make it easy?

Thanks

 

[DonkeyOte]

You can replace the inline array {10,5} with a horizontal vector containing the appropriate rates (eg A1:B1) - note the second value is incremental from the first (ie 10,10+5)

 

[rjs1943]

Hello all

What do the numbers in the inline array mean? Is the 10 the 10 dollars an hour, and the 5, time and a half? I have no idea what all this means. Could you show me some different examples. I don't understand what or how to use an inline array. I just want to go from $10.00 an hour to say 15 an hour at double time.

Thanks you very much

 

[DonkeyOte]

@rjs1943 - at this stage I would suggest you create your own thread detailing your own requirements.

 

[wbeverett]

I used the "streamlined" version.

No entry into C12 generated "360" into C13. This occured on the first few days, I expect up to 40 hours, not on the last few days.

I placed another "IF" around the formula.

=IF(C12>0,...Streamlined Version...,0)

I would prefer a null so I am going to look at adding the null character instead of 0.
Thanks!