Hourly Employee Punch In/Out

meppwc

Well-known Member
Joined
May 16, 2003
Messages
604
Office Version
  1. 365
Platform
  1. Windows
I work for a company that wants me to keep my hours EXACTLY at 40 per week, right down to the minute.
I am allowed to work a little less or more each day but when Friday comes I have to adjust everything to meet that 40 hour/0 minute expectation for the week.

So I am thinking there must be a way of calculating this using Excel.
I work Mon--Fri during the day
There are always 4 punches per day -- In, Out-Lunch, In-Lunch, Out
What I would like to do is put into Excel my punch times (hh:mm) while seeing a sum of how many hours/minutes I have worked for the week.
Then when Friday comes along, and I have 3 of 4 Friday punches in, tweak my hours to exactly 40 by punching out Friday to the minute.
What I am hoping for is Excel to be able to tell me to the minute what time to punch out for the day on Friday so that the week equals 40 hours / 0 minutes.

Can someone please help me with how to accomplish this
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Something like this?

Out time on Friday (O11) is the time you should clock out. Based off the Total hours from M-T plus morning hours on Friday..



Excel 2010
ABCDEFGHIJKLMNOPQRST
1Week starting:1/1/2017
2Week ending:1/7/2017
3
4
5SundayMondayTuesdayWednesdayThursdayFridaySaturdayScheduled
Hours
61/1/20171/2/20171/3/20171/4/20171/5/20171/6/20171/7/2017
7Time In Total 7:35 AM Total 7:30 AM Total 7:30 AM Total 7:30 AM Total 7:30 AM Total Total 40.00
8Time Out0.00 12:00 PM4.42 12:00 PM4.50 12:00 PM4.50 12:00 PM4.50 12:00 PM4.50 0.00 + Hours
9Meal Break0.00
10Time In Total 1:00 PM Total 1:00 PM Total 1:00 PM Total 1:00 PM Total 1:05 PM Total Total Total Hours
11Time Out0.00 5:00 PM4.00 5:00 PM4.00 5:00 PM4.00 5:00 PM4.00 2:40 PM1.58330.00
12Total0.00 8.42 8.50 8.50 8.50 6.08 0.00 40.00
13
14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
S2=IF($S1=0,"",$S1+6)
E6=IF($S1=0,"",$S1)
G6=IF($S1=0,"",$S1+1)
I6=IF($S1=0,"",$S1+2)
K6=IF($S1=0,"",$S1+3)
M6=IF($S1=0,"",$S1+4)
O6=IF($S1=0,"",$S1+5)
Q6=IF($S1=0,"",$S1+6)
F8=IF((OR(E8="",E7="")),0,IF((E8<E7),((E8-E7)*24)+24,(E8-E7)*24))
H8=IF((OR(G8="",G7="")),0,IF((G8<G7),((G8-G7)*24)+24,(G8-G7)*24))
J8=IF((OR(I8="",I7="")),0,IF((I8<I7),((I8-I7)*24)+24,(I8-I7)*24))
L8=IF((OR(K8="",K7="")),0,IF((K8<K7),((K8-K7)*24)+24,(K8-K7)*24))
N8=IF((OR(M8="",M7="")),0,IF((M8<M7),((M8-M7)*24)+24,(M8-M7)*24))
P8=IF((OR(O8="",O7="")),0,IF((O8<O7),((O8-O7)*24)+24,(O8-O7)*24))
R8=IF((OR(Q8="",Q7="")),0,IF((Q8<Q7),((Q8-Q7)*24)+24,(Q8-Q7)*24))
S9=S12-S7
F11=IF((OR(E11="",E10="")),0,IF((E11<E10),((E11-E10)*24)+24,(E11-E10)*24))
H11=IF((OR(G11="",G10="")),0,IF((G11<G10),((G11-G10)*24)+24,(G11-G10)*24))
J11=IF((OR(I11="",I10="")),0,IF((I11<I10),((I11-I10)*24)+24,(I11-I10)*24))
L11=IF((OR(K11="",K10="")),0,IF((K11<K10),((K11-K10)*24)+24,(K11-K10)*24))
N11=IF((OR(M11="",M10="")),0,IF((M11<M10),((M11-M10)*24)+24,(M11-M10)*24))
O11=O10+TIME(0,(P11*60),0)
P11=S7-(SUM(G12,I12,K12,M12)+P8)
R11=IF((OR(Q11="",Q10="")),0,IF((Q11<Q10),((Q11-Q10)*24)+24,(Q11-Q10)*24))
E12=IF(OR(ISTEXT(F8),ISTEXT(F11)),"Error in C12 or C15",(F8+F11))
G12=IF(OR(ISTEXT(H8),ISTEXT(H11)),"Error in C12 or C15",(H8+H11))
I12=IF(OR(ISTEXT(J8),ISTEXT(J11)),"Error in C12 or C15",(J8+J11))
K12=IF(OR(ISTEXT(L8),ISTEXT(L11)),"Error in C12 or C15",(L8+L11))
M12=IF(OR(ISTEXT(N8),ISTEXT(N11)),"Error in C12 or C15",(N8+N11))
O12=P11+P8
Q12=IF(OR(ISTEXT(R8),ISTEXT(R11)),"Error in C12 or C15",(R8+R11))
S12=SUM(E12:R12)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
You could also do it this way:

PunchTimes.xlsx


ABCDEFG
1Hours
2RawTargetWorkedRemainingLeaveAt
34040:0036:193:4116:56
4
5DayInOut-LunchIn-LunchOutPunchesWorked
6Lunes07:0012:1213:1816:36408:30
7Martes08:0212:0613:3617:1247:40
8Miércoles08:1812:3013:2416:4247:30
9Jueves08:3412:4214:0017:4847:56
10Viernes07:4712:3013:1534:43
11Average07:5612:2413:3017:04Total36:19

<tbody>
</tbody>
Hoja1

Worksheet Formulas
CellFormula
F6=COUNT(Punch[@[In]:[Out]])
G6=IF([Punches]>1,[Out-Lunch]-[In])+IF([Punches]=4,[Out]-[In-Lunch])
B3=VALUE(RawHours&":00")
C3=AGGREGATE(9,2,Punch[Worked])
D3=TargetHours-WorkedHours
E3=IFERROR(CHOOSE(FridayPunches,RemainingHours+$B$10,"Bon Apetit",RemainingHours+$D$10,"Have a nice WeekEnd"),"No idea")

<tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
FridayPunches=Hoja1!$F$10
RawHours=Hoja1!$A$3
RemainingHours=Hoja1!$D$3
TargetHours=Hoja1!$B$3
WorkedHours=Hoja1!$C$3

<tbody>
</tbody>

<tbody>
</tbody>

Hope it helps.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top