# Calculate Clock in and Clock out Times

#### solace94

##### New Member
I have an issue I am trying to solve and having a hard time finding a solution and hoping someone can point me in the right direction.

I have a list of seasonal employees who will be clocking in by using an excel template. They scan the bar code on the back of their badge and the excel will time stamp the scan. I want to try and get it to also catagorize the scans are clock in or clock out based on their last scan.

 # Name Timestamp In/Out 1 Mike 9/17/2014 8:00 2 Ryan 9/17/2014 8:30 1 Mike 9/17/2014 11:00 1 Mike 9/17/2014 11:30 2 Ryan 9/17/2014 11:30

<tbody>
</tbody>

In the example the first and 2nd entries would be both IN, 3rd entry would be OUT, 4th IN and 5th OUT.

Would like to find a way to create a formula to alternate In and Out based on the previous punch or even In 1, Out 1, In 2, Out 2. This would allow me to see if there were any missed punches and be able to calculate each persons work time. Not sure if this can be done but any assistance as to method or formula would be great.

Much Appreciated to all who help.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### NeonRedSharpie

##### Well-known Member
Code:
``=IF(MOD(COUNTIF(\$B\$2:B2,B2),2) = 0,"Out","In")``

Put this in D2 and copy down. It won't account for missed clock ins, because I don't know how you want to do that.

#### Scott Huish

##### MrExcel MVP
=IF(MOD(COUNTIF(\$B\$2:B2,B2),2),"IN","OUT")

Copy down.

Replies
1
Views
259
Replies
3
Views
295
Replies
1
Views
385
Replies
12
Views
532
Replies
4
Views
306

1,195,923
Messages
6,012,317
Members
441,690
Latest member
CyberWrek

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