How To Combine Date And Time Columns Into One Column In Google Sheets?

Many professionals use Google Sheets to keep a record of time and date for HR and other purposes. While manually entering the values can take a lot of time, you can use the in-built features in Google Sheets to combine values in one cell.

With this tutorial, we tell you how to combine date and time values in separate columns into just one with 2 very easy techniques. These methods provide flexibility and convenience, enabling you to merge date and time information into a single column quickly.

Follow the steps to learn a new skill today!

Method 1: Using the TEXT Function

The TEXT function in Google Sheets allows you to format and display the values of a cell or range with custom formatting. Here's how you can combine date and time columns using the TEXT function:

Step 1: Select an empty cell next to the time and date columns.

Step 2: In the active cell, enter or paste the formula.

Google Sheets formula to combine date and time values in one cell using TEXT function: =TEXT(A2, "mm-dd-yyyy") & " | " & TEXT(B2, "hh:mm")

Make sure you change the date and time format according to your spreadsheet. This formula combines the date from cell A2 and the time from cell B2. Adjust the cell references as needed.

Step 3: Press Enter to apply the formula. The date and time values will be combined into a single cell in the new column.

Step 4: Drag the formula cell down to fill the remaining cells in the column, and the date and time combination will continue for each corresponding row.

Method 2: Using the CONCATENATE Function

You can also club dates and times using the CONCATENATE function in Google Sheets. This easy formula is modified with a dash (-) as a separator. Follow the steps to learn more about method 2.

Step 1: Select the cell in a column where you want to combine the date and time.

Step 2: Enter the formula into the active cell.

Google Sheets formula to combine date and time with dash as a separator in one cell: =concatenate(text(A2,"mm/dd/yyyy")&" - "&text(B2,"hh:mm"))

Step 3: Drag the blue box in the bottom right corner of the active cell down to replicate the action.

The final table will look like this,

Understanding the Formulae

In both formulas, the CONCAT and TEXT function combines the time and date format in one cell. You can change the separator between the different values using space, comma, or a specific text.

Remember to modify the date and time format as per the data in your spreadsheet.

Conclusion

Combining date and time columns into a single column in Google Sheets enhances data recording for longer periods and also simplifies the analysis. Whether you choose to use the TEXT or the CONCAT function, these methods allow for the seamless merging of date and time values.

Adjust the formulas based on your specific column references and formatting requirements. This technique is useful for creating a unified timestamp or for further analysis and manipulation of date and time data.

Learn more about such nifty Excel tricks on Tutorials Point today!