In this article:

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. 

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

Supercharge Your Spreadsheets with Lido

🚀 Import data from anywhere and build custom tools and powerful dashboards straight from your spreadsheet. Discover what's possible in our Build Gallery.


Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!