Creating a time card with overtime and double time please help me i need to finish this

[josebrizo]

Hello friends
Thank you very much in advance to anyone who helps me on getting this time card ready, ive been trying and trying different methods and i just cant figure it out..

I am creating a time card for my work as i cant punch in or out in office, i have to do it at home and then email it out to the office and i need it to be easy to use and calculate by itself.

E11 is Time In
F11 is Lunch Out
G11 is Lunch Return
H11 is Time Out.

thank you so much to anyone that is willing to help me on this

Thank you everyone!! on this forums

 

[jtakw]

Hi,

You didn't give any specifics...

So, assuming your hours Don't go past midnight, you Don't get paid for lunch, and you don't work Over 24 hours:

Book1
	D	E	F
11	Time In	8:00 AM
12	Lounch Out	12:00 PM
13	Lounch Return	1:00 PM
14	Time Out	5:00 PM
15	Net Hours worked	8:00	<--Format as h:mm
Sheet437

Cell Formulas
Range	Formula
E15	=E14-E11-(E13-E12)

 

[josebrizo]

Hello I’m sorry, sometimes I start work from 4 am get 30 minutes lunch and stop work at 5 pm let say that would give me 4 hours overtime and .50 of double time.. I need a formula based on that schedule to calculate my double time and also overtime ....

Hi,

You didn't give any specifics...

So, assuming your hours Don't go past midnight, you Don't get paid for lunch, and you don't work Over 24 hours:

Book1
	D	E	F
11	Time In	8:00 AM
12	Lounch Out	12:00 PM
13	Lounch Return	1:00 PM
14	Time Out	5:00 PM
15	Net Hours worked	8:00	<--Format as h:mm
Sheet437

Cell Formulas
Range	Formula
E15	=E14-E11-(E13-E12)

 

[Dave Patton]

Excel 2010
	D	E
11	Time In	4:00 AM
12	Time Out	5:00 PM
13	Lunch	0:30
14	Net Hours worked	12.50
15	Regular	8.00
16	Double	0.50
17	Overtime	4.00
18
19	T201901a	1a
20
1a

Cell Formulas
Range	Formula
E14	=(E12-E11-E13)*24
E15	=MIN(E14,8)
E16	=MAX(0,E14-12)
E17	=E14-E15-E16

 

[n1lesh]

Excel 2010

	D	E
T201901a	1a

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Time In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]4:00 AM[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Time Out[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]5:00 PM[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Lunch[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]0:30[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Net Hours worked[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]12.50[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Regular[/TD]
[TD="align: right"]8.00[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Double[/TD]
[TD="align: right"]0.50[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Overtime[/TD]
[TD="align: right"]4.00[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

1a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E14[/TH]
[TD="align: left"]=(E12-E11-E13)*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E15[/TH]
[TD="align: left"]=MIN(E14,8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E16[/TH]
[TD="align: left"]=MAX(0,E14-12)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E17[/TH]
[TD="align: left"]=E14-E15-E16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

can you please explain the double overtime and normal overtime
after how many hours do you get double overtime and how many hours normal overtime??

 

[josebrizo]

Hello.. in California the law is, that after your 8 hours you get over time.. but during overtime if you work 4 hours is called over time pays time and a half .. now if you work past those 4 hours you get pay double time.. let say instead of $30 hour you’ll get $60.. but if you work within those rules..same day.. or overtime after your 40 hours of the week

Excel 2010

	D	E
T201901a	1a

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Time In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]4:00 AM[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Time Out[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]5:00 PM[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Lunch[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]0:30[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Net Hours worked[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]12.50[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Regular[/TD]
[TD="align: right"]8.00[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Double[/TD]
[TD="align: right"]0.50[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Overtime[/TD]
[TD="align: right"]4.00[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

1a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E14[/TH]
[TD="align: left"]=(E12-E11-E13)*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E15[/TH]
[TD="align: left"]=MIN(E14,8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E16[/TH]
[TD="align: left"]=MAX(0,E14-12)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E17[/TH]
[TD="align: left"]=E14-E15-E16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

can you please explain the double overtime and normal overtime
after how many hours do you get double overtime and how many hours normal overtime??

 

[Dave Patton]

Did post # 4 answer your question?
If not, please explain.

 

[josebrizo]

Hello.. in California the law is, that after your 8 hours you get over time.. but during overtime if you work 4 hours is called over time pays time and a half .. now if you work past those 4 hours you get pay double time.. let say instead of $30 hour you’ll get $60.. but if you work within those rules..same day.. or overtime after your 40 hours of the week


YOU GUYS ARE REALLY ONE BIG HELP ON THIS SUBJECT, I REALLY APPRECIATE YOUR INPUTS.. THANK YOU VERY MUCH.

 

[Dave Patton]

I show 2 Versions/alternatives.
Ensure that the example is appropriate considering the
payroll legislation in your area; edit the formulas if necessary.

Nonexempt employees must be paid daily overtime as follows: One and one-half times the employee's regular rate of pay for all hours worked in excess of 8 hours, up to and including 12 hours in any workday, and for the first 8 hours worked on the seventh consecutive day of work in a workweek.

What is a work week?What hours are at 1.5?What hours are at 2.0?What rates apply to Saturday?
What rates apply to Sunday?What are rates after 40 hours?

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M
2		Jan 07 19	Jan 08 19	Jan 09 19	Jan 10 19	Jan 11 19	Jan 12 19	Jan 13 19
3	Version 1	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Total	Adj	Total
4	Hours	5	5	7	13	13	8	10	61		61
5	Regular	5	5	7	8	8	8	0	41	-1	40
6	OT @ 2.0	0	0	0	1	1	0	2	4		4
7	OT @ 1.5	0	0	0	4	4	0	8	16	1	17
8
9	Version 2	Day1	Day2	Day3	Day4	Day5	Day6	Day7	Total
10	Hours	5	5	7	13	13	8	10	61
11	OT @ 2.0								4
12	Regular								40
13	OT @ 1.5								17
14
1aa

Cell Formulas
Range	Formula
K4	=SUM(D4:J4)
K5	=SUM(D5:J5)
K6	=SUM(D6:J6)
K7	=K4-K5-K6
K10	=SUM(D10:J10)
K11	=SUMPRODUCT(--(D10:I10>12),D10:I10-12)+(J10>8)*(J10-8)
K12	=MIN(40,SUM(D10:I10)-SUMPRODUCT(--(D10:I10>8),D10:I10-8))
K13	=K10-K11-K12
D5	=MIN(8,D4)*(WEEKDAY(D2,2)<7)
D6	=(D4>12)*(D4-12)+(COUNT($D$2:D2)=7)*(D4>8)*(MIN(4,D4-8))
D7	=D4-D5-D6
E5	=MIN(8,E4)*(WEEKDAY(E2,2)<7)
E6	=(E4>12)*(E4-12)+(COUNT($D$2:E2)=7)*(E4>8)*(MIN(4,E4-8))
E7	=E4-E5-E6
F5	=MIN(8,F4)*(WEEKDAY(F2,2)<7)
F6	=(F4>12)*(F4-12)+(COUNT($D$2:F2)=7)*(F4>8)*(MIN(4,F4-8))
F7	=F4-F5-F6
G5	=MIN(8,G4)*(WEEKDAY(G2,2)<7)
G6	=(G4>12)*(G4-12)+(COUNT($D$2:G2)=7)*(G4>8)*(MIN(4,G4-8))
G7	=G4-G5-G6
H5	=MIN(8,H4)*(WEEKDAY(H2,2)<7)
H6	=(H4>12)*(H4-12)+(COUNT($D$2:H2)=7)*(H4>8)*(MIN(4,H4-8))
H7	=H4-H5-H6
I5	=MIN(8,I4)*(WEEKDAY(I2,2)<7)
I6	=(I4>12)*(I4-12)+(COUNT($D$2:I2)=7)*(I4>8)*(MIN(4,I4-8))
I7	=I4-I5-I6
J5	=MIN(8,J4)*(WEEKDAY(J2,2)<7)
J6	=(J4>12)*(J4-12)+(COUNT($D$2:J2)=7)*(J4>8)*(MIN(4,J4-8))
J7	=J4-J5-J6
L5	=-L7
L7	=(K5>40)*(K5-40)
M4	=K4+L4
M5	=K5+L5
M6	=K6+L6
M7	=K7+L7