As its name suggests, the PUNCH_IN_OUT_SAME_ROW() function copies punch out time to the punch in time row in Google Sheets.

In this post, you will get a custom (named) function to format employee punch data in Google Sheets.

It has two main advantages.

1. Make it possible for employers to calculate the amount of time each employee works by deducting the punch in (clock in) time from the punch out (clock out) time.
2. Allow using FILTER to filter out punch out rows since we have copied the corresponding OUT time to the IN row. It saves the number of pages when printing. 

Prerequisites to Use the PUNCH_IN_OUT_SAME_ROW Custom Function

Please check the prerequisites below for the formula to correctly copy the punch out time to the punch in time row.

1. The above custom-named function for Google Sheets requires three columns: Name or ID, Punch time, and Status.
2.  The Status column must contain either of the keywords “IN” (clock in) or “OUT” (clock out). If you have used some other status codes, replace them with these two.
3.  The table must be sorted first by employed ID or Name and then by punch time. Please see the format and ignore column E for the time being.

Syntax and Arguments

Syntax: PUNCH_IN_OUT_SAME_ROW(timestamp, id, status)

Arguments:

timestamp:- The column that contains the punch time.

id: The employee ID column. Don’t have one? Use the Name column.

status:- The column that contains the statute, i.e., “IN” for the clock in and “OUT” for the clock out.

How to copy the punch out time to the punch in time row in Google Sheets?

Copying Punch Out Time to the Punch In Time Row

Please scroll up and see the image.

You can see the following formula in cell E2 that copies clock out time to the clock in time row.

=PUNCH_IN_OUT_SAME_ROW(C2:C,A2:A,D2:D)

Note:- Array formulas will return #REF error if the output range contains other values. So before entering the above code, empty E2:E).

Where;

timestamp = C2:C

id=A2:A

status=D2:D

We can replace the above formula with the below one where I used id=B2:B (Name).

=PUNCH_IN_OUT_SAME_ROW(C2:C,B2:B,D2:D)

Select the output range and go to the Format menu, and select Number >
Date time.

This way, we can copy the punch out time to the punch in time row using my custom function in Google Sheets.

How to Import PUNCH_IN_OUT_SAME_ROW Function into My Google Sheets?

If you aren’t familiar with importing Named Functions from one Sheet to another, please follow the quick instructions below.

1. Please make a copy of my sample Sheet below.
2. Open your Sheet in which you want to copy the clock out time to the clock in time entered row.
3. In your Sheet, go to the menu Data and select Named functions.
4. In the opened sidebar panel, click “Import function.”
5. Find my copied Sheet by searching its name, i.e., probably “copy of clock in_out II.”
6. Select it and click the “Import” button.
7. Select the function name and import.

clock in_out II

You are ready to use my function and code an array formula as above to copy punch out time to the punch in time row.

How to Use a Formula Instead of the Named Function?

You may want the underlying formula instead of the named function in the following two scenarios.

1. To learn what is happening behind the screen.
2. You have multiple Sheets with employee data and don’t want to import my function in every Sheet. The native formula may be handy for you.

Here is the array formula for the above sample employee data range.

=map(C2:C,D2:D,A2:A,lambda(ac,bc,cc,if(bc<>"IN",,chooserows(ifna(filter(C2:C,A2:A=cc,D2:D="OUT",row(A2:A)>row(ac))),1))))

You can replace the E2 ‘named’ formula with this one. It will also place the clock out time to clock in time row.

Do you want to know how this formula works?

Here is the non-array avatar. You must enter it in E2 and copy-paste it down.

=if(D2<>"IN",,chooserows(ifna(filter(C2:C,A2:A=A2,D2:D="OUT",row(A2:A)>row(A2))),1))

I’ve used MAP, one of the LAMBDA functions, in the array formula to increment A2 to A3, A4, A5, and so on in rows down in the range.

Anatomy of the Formula

I’ve used a simple logic in the non-array formula. Let’s see what it does in E2.

The FILTER part filters all the clock out times if the IDs are equal to A2 and row numbers > row number of A2.

The filtered output may contain more than one row. But the first row will be the clock-out time we are looking for. With the help of CHOOSEROWS, I’ve extracted that value.