How can I combine date and time in Google Sheets?

By /

In Google Sheets, the “DATE” and “TIME” functions can be used to combine date and time into a single cell. The “DATE” function takes in three arguments – year, month, and day – and returns the corresponding date. The “TIME” function takes in three arguments – hour, minute, and second – and returns the corresponding time. By using these functions together, you can create a cell that displays both the date and time. For example, the formula “=DATE(2021, 5, 12)+TIME(9, 30, 00)” would display “5/12/2021 9:30:00” in the cell. This can be useful for tracking events or scheduling tasks in a spreadsheet.

Combine Date & Time in Google Sheets

You can use the following formulas to combine a date and time in separate cells into one cell in Google Sheets:

Method 1: Use Addition

=A2+B2

Method 2: Use CONCATENATE and TEXT

=CONCATENATE(TEXT(A2,"m/d/yyyy")," ",TEXT(B2,"h:mm AM/PM"))

The benefit of using the TEXT function is that you can specify the exact format of the resulting date and time.

Note: Both formulas assume that the date is in cell A2 and the time is in cell B2.

The following examples show how to use each method in practice with the following columns of dates and times in Google Sheets:

Example 1: Combine Date & Time Using Addition

We can type the following formula into cell C2 to combine the date in cell A2 with the time in cell B2:

=A2+B2

We can then click and drag this formula down to each remaining cell in column C:

Google Sheets combine date and time

Column C now displays the date and time in one cell for each row.

By default, Google Sheets does not display the AM or PM for each time and instead displays the hours on a 24-hour clock.

Example 2: Combine Date & Time Using CONCATENATE and TEXT

=CONCATENATE(TEXT(A2,"m/d/yyyy")," ",TEXT(B2,"h:mm AM/PM"))

We can then click and drag this formula down to each remaining cell in column C:

Column C now displays the date and time in one cell for each row.

Note that we specified mm/dd/yyyy as the format for the date and h:mm AM/PM as the format for the time.

Note: You can find the complete documentation for the TEXT function in Google Sheets .

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

x

Related terms:

  1. Combine Date & Time in Google Sheets
  2. How do you Combine the IF and AND Functions in Google Sheets?
  3. How do I combine the IF and OR Functions in Google Sheets
  4. How can I convert a date in YYYYMMDD format to a more readable date format in Google Sheets?
  5. Combine Date & Time in Excel (2 Methods)
  6. How do I combine two date and time columns into one in a Pandas dataframe?
  7. Count Specific Characters in Google Sheets How to count specific characters in google sheets?
  8. How can I combine rows with the same column values in PySpark?
  9. How can I use Google Sheets to calculate age from a given date of birth?
  10. What is the method to find the most recent date in Google Sheets?