Separate Date and Time from a Timestamp in Google Sheets

Timestamps are DateTime values in Google Sheets. These are combined values storing date and time. There are instances when we only need the date, not the time and vice versa. 

In this tutorial, we will learn 3 methods for separating date and time from timestamps in google sheets. 

Suppose you have the following table with Timestamp (Date and Time) on Column A, and you want to separate the date and time into columns B and C, respectively. 

Method 1: Use SPLIT Function

The SPLIT function can split the contents of a cell into multiple cells. Since the timestamp is composed of Date and Time, we can use the SPLIT function to extract the Date to one cell and Time to another cell. 

Here are the steps:

1. On cell B2, type =SPLIT(A2, “ ”). This will automatically write the date in cell B2 and the time in cell C2. Don’t forget the space between the “ ”. Otherwise, you’ll get an error. 
2. Copy these formulas to other cells in column B.

Method 2: Use INT Function

In the internal google sheet system, the date is stored as a whole number and the time is stored as a decimal or as a fractional part. As such, we can extract the date from the timestamp by getting the whole number and the time by retrieving the fractional part. 

To do that, we can use the INT function. The INT function rounds a number down to the nearest integer.

Here are the steps:

1. To get the date:

1. On cell B2, type =INT(A2). 
2. If the format is still not in the date format, 

1. Click cell B2. Go to Format, click Number, then click Date. 

‍

2. Click Cell B2. Go to More Formats, then click Date. 

‍

3. Copy the formula in cell B2 to other cells in column B. 

2. To get the time: 

1. On cell C2, type =A2-B2. 
2. If the format is still not in the date format, you can do either of the following:

1. Click cell C2. Go to Format. Click Number, then click Time.

‍

2. Click Cell C2. Go to More formats, then click Time.
   

‍

3. Copy the formula in cell C2 to other cells in column C. 
   
   

Method 3: Changing the format of the cells to Date or Time

You can easily separate the date and time by just changing the format of the cells. 

1. Copy the timestamp values on columns B and C. 
2. To get the date, click column B. Then, you can do either of the following:

1. Go to Format. Click Number, then click Date. 
2. Go to More Formats, then click Date.

1. To get the time, click column C. Then, you can also do either of the following:

1. Go to Format. Click Number, then click Time.
2. Go to More Formats, then click Time.

Using any of these 3 methods, our first table above will become 

‍

If you wish to change the format of the date and time, highlight the cells you wish to change, go to the 123 icon on the spreadsheet toolbar. Click More Formats, and then select More date and time formats. 

‍

After clicking More date and time formats, this window will appear. Choose among the possible date or time formats. Then, click Apply. 

‍

Supercharge Your Spreadsheets with Lido

‍