How To Separate Date And Time From A Timestamp In Google Sheets

Reading Time: 4 minutes

Syntax:
=SPLIT(timestamp, delimeter)

timestamp – Combination of date and time

delimiter – The character or characters to split the text

Sample usage:
=SPLIT(“2009-07-11 02:27:12”, ” “)

// This will separate the date and time values from the timestamp with space (” “) as the separator.

Sample Google Sheets template with formula here

Google Sheets offer us a wide range of tools and features to convert data from one form to another. Multiple instances exist where our data values are given as a timestamp (combination of date and time). In such situations, it might be required to be able to separate date and time from a timestamp in Google Sheets.

In this tutorial, we will go through some ways to separate date and time from a timestamp in Google Sheets. The article comprises of the following sections:

SPLIT function to separate date and time from a timestamp in Google Sheets

The SPLIT function in Google Sheets is used to split the contents of a cell into multiple cells. As a timestamp is composed of date and time, we can use the SPLIT function to separate date and time from a timestamp in Google Sheets. The syntax of the SPLIT function is as follows:

=SPLIT(cell_address, delimiter)

In the following example, we have a speadsheet which consists of a column full of timestamp values. Our objective is to separate date and time from the timestamp values. 

Example to understand how to separate date and time from a timestamp in google sheets

The step-by-step process is as follows:

  • Select the empty cell under the DATE column
Select the empty cell
  • Begin your function with the ‘=’ sign. Type in the ‘SPLIT’. The Google Sheets will prompt this function, press Tab/Enter key to autocomplete. The tooltip guide will appear along with the details.
Type in the formula
  • Enter the cell address and value of delimiter. In this case, the value of delimiter is “ “ as we are splitting the timestamp where there is a space between date and time. The formula should look like this:
=SPLIT(A4, “ “)
Fill in the parameters
  • Press the Enter key and the date and time will appear in their respective columns
Date and time values get displayed
  • Drag the formula to fill in the empty column values
Drag the formula to fill in the columns

We have learned how to use SPLIT function to separate date and time from a timestamp in Google Sheets. 

By changing the format of timestamp to date and time 

We can also separate the date and time values of a timestamp by simply formatting the timestamp values to date or time. We will be using the same spreadsheet as our example. The step-by-step procedure is as follows:

  • Select the timestamp values of column A
Select the timestamp column
  • Copy the values in column B and column C
Copy and paste the timestamps in column B and C
  • Select the values of column B
Select the column B
  • Click Format -> Number -> Date
Format the timestamp to date
  • Column B displays the date values of respective timestamps
Column B now has date values
  • Select the values in column C and click Format -> Number -> Time
Select column C and format it to time
  • We get the following output:
final output

We have successfully learned how to use the formatting option to  separate the date and time from a timestamp in Google Sheets. 

Conclusion

We have learned multiple ways to separate date and time from a timestamp in Google Sheets. This way we can easily extract date and time values (according to our requirements). You are now ready to use these tools and techniques to your advantage.

Commonly Asked Questions

How do I use Datevalue in Google Sheets?

The DATEVALUE function in Google Sheets takes a date in text format and returns the corresponding date in numeric format. To use the function, enter the following into a cell: =DATEVALUE(“1/1/2018”) . This will return the date 1/1/2018 in numeric format.

How do I convert a date to a string in Google Sheets?

If you’re entering a new date into a cell, simply start the date with an apostrophe (‘). By starting a cell with an apostrophe you tell Excel to trade that cell as text. This keeps the date exactly as entered and prevents it from being converted to a date stored as a serial number.

See Also

You have successfully learned how to separate date and time from a timestamp in Google Sheets. Are you interested in learning more about how much you can succeed with Google Sheets? With so many powerful features of Google Sheets, you save time and effort.

We have several tutorials that cover tricks and tips in Google Sheets. You can discover them here.

Here are some articles you might be interested in:

https://blog.tryamigo.com/how-to-use-sort-function-in-google-sheets/

https://blog.tryamigo.com/sort-query-using-order-by-in-google-sheets/

https://blog.tryamigo.com/introduction-to-date-function-in-google-sheets/