Whether you have a login and logout date-time or time in and time out date-time in a column in two separate rows, you can consolidate them into the same row.

We will use a combination formula mainly based on FILTER and REDUCE to combine time in and time out into the same row in Google Sheets.

Features:

1. The formula can combine time in and out for multiple persons/IDs.
2. It can handle multiple time-ins and time-outs by the same person/ID.
3. No issue if only time in is recorded, and time out is pending.
4. The formula returns the entire result as a table.

Drawback:

The formula has one drawback. If you have a very large data set, the formula may fail as it utilizes one of the lambda helper functions, REDUCE.

In that case, you may consider splitting your source data into multiple tables and using multiple formulas.

You May Like:- How to Create Self-Formatting Tables in Google Sheets (With a Simple Initial Setup)

Sample Data and Requirements

For our sample data, we need three columns: a timestamp column, an ID column, such as employee ID or Name, and a status column containing the strings “In” or “Out.”

In our sample data, column A contains timestamps (in and out times), column B contains the names of a few employees, and in column C, you can find the recorded time as either “In” or “Out” (status).

We will implement an array formula in cell E1 that consolidates the time in and out for all individuals in this sample data into their respective rows.

The formula will return the persons’ names in one column and their time in and time out in two separate columns. Please refer to the image below for the combined in and out results.

How to Combine Time In and Time Out into the Same Row in Google Sheets

Here is the ‘hidden’ formula in cell E1 that returns the results in columns E to I in the order of “Name,” “Time In,” “Status,” “Time Out,” and “Status.”

=LET(time, A2:A, id, B2:B, status, C2:C, REDUCE(
     HSTACK("Name", "Time In", "Status", "Time Out", "Status"), 
     TOCOL(UNIQUE(id), 3), 
     LAMBDA(accu, row, 
          IFERROR(VSTACK(
               accu, 
               HSTACK(
                    SORT(FILTER(HSTACK(id, time, status), id=row, status="In")), 
                    SORT(FILTER(HSTACK(time, status), id=row, status="Out"))
               )
          ))
     )
))

This formula tends to convert timestamps to date values due to the use of the IFERROR function. Therefore, when applying this formula, select the login/logout or time in/time out columns in the result range and apply Format > Number > Date time.

In the formula explanation section below, I have provided details on how this formula combines the time in and time out into the same row in Google Sheets.

The Logic Behind the Formula that Combines Time In and Time Out in Google Sheets

The logic of the formula that combines time in and time out into the same row is very simple. What makes it complex is the application of the logic using the REDUCE Lambda function.

There are two filters in the formula:

The first filter selects the rows matching a particular person with the status “In” and sorts them, placing the earliest records at the top.

The second filter selects the rows matching the same person with the status “Out” and sorts them, placing the earliest records at the top.

Stack these results horizontally. The outcome will be the combined in and out times for a particular person. We use REDUCE to efficiently apply the logic to the time in and out for all persons.

Breaking Down the Formula:

We have utilized the LET function to assign meaningful names to each column in the data. Here is the syntax of the LET function and the ranges with their assigned names.

Syntax:

LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)

Names and Value Expressions:

time, A2:A,
id, B2:B,
status, C2:C

We will use the names (time, id, and status) instead of the ranges (A2:A, B2:B, and C2:C) in the REDUCE.

Formula Expression:

REDUCE(
     HSTACK("Name", "Time In", "Status", "Time Out", "Status"), 
     TOCOL(UNIQUE(id), 3), 
     LAMBDA(accu, row, 
          IFERROR(VSTACK(
               accu, 
               HSTACK(
                    SORT(FILTER(HSTACK(id, time, status), id=row, status="In")), 
                    SORT(FILTER(HSTACK(time, status), id=row, status="Out"))
               )
          ))
     )
)

To understand the formula expression, which is essentially a REDUCE formula, let’s first review its syntax.

REDUCE Syntax:

REDUCE(initial_value, array_or_range, lambda)

Now, let’s break down how the REDUCE formula combines time in and time out into the same row in Google Sheets.

• initial_value: HSTACK("Name", "Time In", "Status", "Time Out", "Status") represents the title we want to return at the top of the result.
• array_or_range: TOCOL(UNIQUE(id), 3) represents unique names.
• LAMBDA(accu, row, …): Defines a lambda function taking two parameters (‘accu’ and ‘row’). The initial value in the ‘accu’ (accumulator) is the title for the result, and the ‘row’ represents each element in the array (unique names).

Here is what the function does:

IFERROR(VSTACK(
               accu, 
               HSTACK(
                    SORT(FILTER(HSTACK(id, time, status), id=row, status="In")), 
                    SORT(FILTER(HSTACK(time, status), id=row, status="Out"))
               )
          ))

It vertically stacks the accumulator value (initially the title row) with horizontally stacked two sorted filtered results.

1. The first FILTER filters the data where ‘id’ (B2:B) equals the current ‘row’ value in TOCOL(UNIQUE(id), 3), and ‘status’ is “In.”
2. The second FILTER filters the data where ‘id’ (B2:B) equals the current ‘row’ value in TOCOL(UNIQUE(id), 3), and ‘status’ is “Out.”

The REDUCE function iterates through each element (represented by ‘row’) in the array (TOCOL(UNIQUE(id), 3)), ensuring that the FILTERs filter all the unique names.

Resources

This tutorial elaborated on combining time in and time out into the same row. Here are some related tutorials that address joining two tables. They also utilize the REDUCE and FILTER combo but in a different fashion.

1. How to Left Join Two Tables in Google Sheets
2. How to Right Join Two Tables in Google Sheets
3. How to Inner Join Two Tables in Google Sheets
4. How to Full Join Two Tables in Google Sheets
5. Master Joins in Google Sheets (Left, Right, Inner, & Full) – Duplicate IDs Solved