Sometimes when working in Google Sheets, you may need to take a timestamp and extract the date from it.
Luckily this is a pretty simple process.
In this tutorial, I will show you how to convert a timestamp to date in Google Sheets.
Table of Contents
Timestamp Basics
There may be times when working in the spreadsheet when you have timestamps that you need to convert to date.
Maybe the timestamps have been entered using the NOW function or the keyboard shortcut for entering a timestamp which is ALT + CTRL + SHIFT + ;(semicolon).
Here is an example of a few timestamps entered into cells:
Timestamps are what are called DateTime values.
These are a type of value that stores both date and time.
However, if you want to use the date for a calculation, or just log the date independently and remove the time value, you will need to learn how to convert these to dates. I will cover this in the next section.
Convert Timestamp to Date
Format Timestamp to Date
One of the simplest ways to turn your timestamp into a date is to just format it as a date. This will remove the time part of the cell so you are only left with the date part displaying.
Here are the steps to do this:
1. Highlight the cells that contain the timestamps that you want to format as dates
2. In the top menu select Format>Number
3. From the drop-down menu select Date
4. You should now see only the date remaining in the cells you have highlighted in step 1
One thing to be aware of is that with this method the time still exists in the cell, it is just no longer visible. Look at how in the image above you can see in the top right corner that the time data is still in the cell, just no longer displaying.
This means that if you need to perform calculations with this value, you may not be able to.
To perform calculations with your timestamps you will need to truly extract the date from the timestamps, which I will show you in the next 2 methods.
Extract Date Using TO_DATE
If you want to actually convert your timestamps to dates with a formula you can use a combination of the TO_DATE and INT functions.
The formula to do this will be:
=TO_DATE(INT(timestamp))
- timestamp – this is the cell that contains your timestamp
Here are some examples of how this looks in your spreadsheet:
The reason that you have to use the INT function in this formula is that Google Sheets stores Date and DateTime values as numbers. If you were not to use the INT part of the formula, the time would be stored as a decimal value. The INT will remove this so you are left with only a date remaining.
Convert Timestamp to Date Using SPLIT
Another easy method to convert your timestamps to dates is to use the SPLIT function to split your cell on the spaces. This will put your dates in one cell, and the time part into another cell.
This is the syntax you will need to use for this:
=SPLIT(timestamp,” “)
- timestamp – the cell containing your timestamp
This formula will split cells into two cells splitting them by the space. Since the space is in between the date and time you will end up with dates in one cell, and times in another cell.
Here is an example of how this looks:
Closing Thoughts
As with anything in Google Sheets, there are multiple methods to removing the time from your timestamps and only returning dates. If you just want it to visually appear as a date and don’t care about performing any other calculation with the date, formatting as a date is a quick and easy way.
However, if you actually want to use this date for something else, you will need to use method 2 or 3 which will store your values as actual dates.
We hope you found this tutorial helpful!
More Google Sheets Tutorials:
How to Convert Text to Date
How to Calculate Time
How to Insert Current Date