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
- Format options to separate date and time from a timestamp
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.
The step-by-step process is as follows:
- Select the empty cell under the DATE column
- 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.
- 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, “ “)
- Press the Enter key and the date and time will appear in their respective columns
- Drag the formula to fill in the empty column values
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
- Copy the values in column B and column C
- Select the values of column B
- Click Format -> Number -> Date
- Column B displays the date values of respective timestamps
- Select the values in column C and click Format -> Number -> Time
- We get the following 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/