Separate Date and Time from a Timestamp in Google Sheets
January 13, 2025
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:
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.
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:
To get the date:
On cell B2, type =INT(A2).
If the format is still not in the date format,
Click cell B2. Go to Format, click Number, thenclick Date.
Image: Format menu. Number and date options are circled.
Click Cell B2. Go to More Formats, thenclick Date.
Image: More format menu. The date option is circled.
Copy the formula in cell B2 to other cells in column B.
To get the time:
On cell C2, type =A2-B2.
If the format is still not in the date format, you can do either of the following:
Click cell C2. Go to Format. Click Number, then click Time.
Image: Format menu. Number and time are circled.
Click Cell C2. Go to More formats, then click Time.
Image: More format menu. The time option is circled.
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.
Copy the timestamp values on columns B and C.
To get the date, click column B. Then, you can do either of the following:
Go to Format. Click Number, then click Date.
Go to More Formats, then click Date.
To get the time, click column C. Then, you can also do either of the following:
Go to Format. Click Number, then click Time.
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.
Turn PDFs into spreadsheet tables with AI
Use Lido to extract data from contracts, invoices, financials, and more in seconds. Save time, reduce errors.
Lido doesn't work on mobile yet, but we'll send you an email with your login details for when you're back at a desktop!
Oops! Something went wrong while submitting the form.
Lido doesn't work on mobile yet, but we'll send you an email with your login details for when you're back at a desktop!
Oops! Something went wrong while submitting the form.