Overtime formula

[dledgington]

Hello. I'm trying to create a time sheet that tallies overtime hours after 40 hours worked, with sick time, vacation and holidays, where none of these can put one into overtime. if anyone can help me with this formula, or knows of an existing template, it would be greatly appreciated! Thank you.

 

[Prevost]

Hi, you may need to provide a little more information to get more responses.

 

[dledgington]

Sorry. I'm new to this. How do I post the template that I'm using?

 

[mrmmickle1]

Maybe something like this would work?:

Excel 2012
	A	B	C	D	E	F
1	Regular Hours	Sick	Vacation	Holiday	Overtime	Total
2	40	8	0	0	0	48
3	32	8	8	0	0	48
4	45	8	0	0	5	53
5	48	8	8	0	8	64
6	32	8	8	8	0	56
Sheet1

Cell Formulas
Range	Formula
E2	=IF(A2>=40,A2-40,0)
E3	=IF(A3>=40,A3-40,0)
E4	=IF(A4>=40,A4-40,0)
E5	=IF(A5>=40,A5-40,0)
E6	=IF(A6>=40,A6-40,0)
F2	=SUM(A2:D2)
F3	=SUM(A3:D3)
F4	=SUM(A4:D4)
F5	=SUM(A5:D5)
F6	=SUM(A6:D6)

 

[dledgington]

I have a template, just not sure how to show or share it.

 

[Prevost]

You can take a screen shot of your program/spreadsheet and then upload it to tinypic.com. It then gives you a bunch of links that you can copy and paste and one of the links is for forums and it will show the screen shot like mrmmickel1 has done. You can also search how to do it and there are posts explaining in more detail how to do it. I use tinypic and it's easy to post pictures.

 

[dledgington]

Ok. Here is the formula that I'm using. It calculates overtime after 8 hours.

=IF((((D10-C10)+(F10-E10))*24)>8,8,((D10-C10)+(F10-E10))*24)

 

[mrmmickle1]

dledgington,

This formula gives a little more insight, but I think what Prevost and I are referring to is to the structure of the spreadsheet. I can see the formula but what numbers are contained in Columns: C,D,E & F?? Are these Vacation, Holiday, Regular hours?? Do these Columns have formulas in them? Are they just numbers? The more information you can give us the better we can address your specific question.

 

[dledgington]

Let me try this.

 

[mrmmickle1]

dledgington,

See if the below formulas will work for your purposes. In order for the calculations to come up correctly you will need to format the cells on the sheet as follows:

C9:F15 = Time Format
G9:G16 = Custom Format - h
G17:G18 & H9:K18 = Number Format

Excel 2012
	B	C	D	E	F	G	H	I	J	K
8	Day	In	Out	In	Out	Regular Hrs.	Overtime Hrs.	Sick Hrs.	Vacation Hrs.	Total Pay
9	Monday	8:00:00 AM	11:00:00 AM	12:00:00 PM	9:00:00 PM	12	4.00	-	-	180.00
10	Tuesday	8:00:00 AM	11:00:00 AM	12:00:00 PM	5:00:00 PM	8	-	4.00	-	120.00
11	Wednesday	-	-	-	-	-	-	-	-	-
12	Thursday	-	-	-	-	-	-	-	-	-
13	Friday	-	-	-	-	-	-	-	-	-
14	Saturday	-	-	-	-	-	-	-	-	-
15	Sunday	-	-	-	-	-	-	-	-	-
16					Total Hrs	20	4.00	4.00	0.00	28.00
17					Hourly Rate	15.00	22.50	15.00	15.00	-
18					Total Pay	300.00	90.00	60.00	0.00	450.00
OvertimeSheet

Cell Formulas
Range	Formula
K9	=IFERROR((G9*$G$17)*24, "-")
K10	=IFERROR((G10*$G$17)*24, "-")
K11	=IFERROR((G11*$G$17)*24, "-")
K12	=IFERROR((G12*$G$17)*24, "-")
K13	=IFERROR((G13*$G$17)*24, "-")
K14	=IFERROR((G14*$G$17)*24, "-")
K15	=IFERROR((G15*$G$17)*24, "-")
K16	=G16*24+SUM(H16:I16)
K18	=SUM(G18:J18)
I16	=SUM(I9:I15)
I18	=(I16*I17)
J16	=SUM(J9:J15)
J18	=(J16*J17)
G9	=IFERROR((D9-C9)+(F9-E9), "-")
G10	=IFERROR((D10-C10)+(F10-E10), "-")
G11	=IFERROR((D11-C11)+(F11-E11), "-")
G12	=IFERROR((D12-C12)+(F12-E12), "-")
G13	=IFERROR((D13-C13)+(F13-E13), "-")
G14	=IFERROR((D14-C14)+(F14-E14), "-")
G15	=IFERROR((D15-C15)+(F15-E15), "-")
G16	=SUM(G9:G15)
G18	=(G16*G17)*24
H9	=IFERROR(IF(AND(I9="-", J9="-"),(G9*24)-8, "-"),"-")
H10	=IFERROR(IF(AND(I10="-", J10="-"),(G10*24)-8, "-"),"-")
H11	=IFERROR(IF(AND(I11="-", J11="-"),(G11*24)-8, "-"),"-")
H12	=IFERROR(IF(AND(I12="-", J12="-"),(G12*24)-8, "-"),"-")
H13	=IFERROR(IF(AND(I13="-", J13="-"),(G13*24)-8, "-"),"-")
H14	=IFERROR(IF(AND(I14="-", J14="-"),(G14*24)-8, "-"),"-")
H15	=IFERROR(IF(AND(I15="-", J15="-"),(G15*24)-8, "-"),"-")
H16	=SUM(H9:H15)
H17	=G17*1.5
H18	=(H16*H17)