# Hourly Employee Punch In/Out

#### meppwc

##### Well-known Member
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### keiserj

##### Board Regular
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

</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)

</tbody>

<tbody>
</tbody>

#### FranzV

##### Board Regular
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:

Replies
3
Views
1K
Replies
1
Views
250
Replies
3
Views
3K
Replies
11
Views
347
Replies
6
Views
257

### Forum statistics

1,191,687
Messages
5,988,024
Members
440,125
Latest member
vincentchu2369 ### 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.

### Which adblocker are you using?    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

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