Separate Date and Time from a Timestamp in Google Sheets

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

Table of Contents
  1. Method 1: Use SPLIT Function
  2. Method 2: Use INT Function
  3. Method 3: Changing the format of the cells to Date or Time
Table of contents
Chapter 1
Chapter 2
Chapter 3
  1. Method 1: Use SPLIT Function
  2. Method 2: Use INT Function
  3. Method 3: Changing the format of the cells to Date or Time
5 Minutes

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. 

Image: A sample table with timestamp on column A


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. 
Image: Format menu. Number and date options are circled.


  1. Click Cell B2. Go to More Formats, then click Date. 
Image: More format menu. The date option is circled. 

  1. Copy the formula in cell B2 to other cells in column B. 
  1. 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.
Image: Format menu. Number and time are circled.


  1. Click Cell C2. Go to More formats, then click Time.
Image: More format menu. The time option is circled.

  1. 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 

Image: A sample table where date and time are separated in columns B and C


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

Image: Changing the 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

Image: Custom date and time formats menu


Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

November 14, 2021

Separate Date and Time from a Timestamp in Google Sheets

Google Sheet spreadsheet

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. 

Image: A sample table with timestamp on column A


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. 
Image: Format menu. Number and date options are circled.


  1. Click Cell B2. Go to More Formats, then click Date. 
Image: More format menu. The date option is circled. 

  1. Copy the formula in cell B2 to other cells in column B. 
  1. 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.
Image: Format menu. Number and time are circled.


  1. Click Cell C2. Go to More formats, then click Time.
Image: More format menu. The time option is circled.

  1. 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 

Image: A sample table where date and time are separated in columns B and C


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

Image: Changing the 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

Image: Custom date and time formats menu


Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.