In this tutorial, you will learn how to convert timestamp values to dates in Google Sheets.
How To Convert Timestamp To Date in Google Sheets
Quick Navigation
Timestamps are a great way to keep track of data over time, as they give us an exact record of when something happened. However, there might be instances where you need just the date portion of a timestamp value
For example, you may want to create a new field in your table called order_date. Since we are only concerned with the date, we must find a way to convert our timestamp value into a date value.
In this guide, we will show you how to convert a timestamp to a more readable date format in Google Sheets. We will do this by using the built-in number formatting options in Google Sheets. We will also explain how you can use the INT and TO_DATE functions together to “round off” a timestamp so that it will drop any time values.
How To Extract Date from Timestamp In Google Sheets
Here’s how to extract dates from timestamp values in Google Sheets.
Step 1
First, we will explain how to directly convert a timestamp value to a date using built-in number formatting.
We’ll start by selecting the entire range of timestamp values. In this example, we will select the timestamp values in A2:A5.
Step 2
Next, select the option Format > Number > Date.
You may also choose the option Format > Number > Custom date and time to find more options on how to display your date value.
Step 3
Your timestamp should now be formatted as a date.
Do note that this method only changes how the values in the range are displayed. The actual values will not be affected.
Step 4
Next, we’ll use the TEXT function to convert timestamp values into dates.
Create a new column and type the string “=TEXT(“ to start the TEXT function.
Step 5
The TEXT function requires two arguments: the number to format, and the output format itself.
In the example above, we’ll select our timestamp value in column A. We want to output this timestamp value in the format “yyyy-mm-dd”.
Step 6
Hit the Enter key to evaluate the result. Use the Fill Handle to fill out the rest of the column.
We can modify the second argument as needed to output different date formats, as seen below.
Step 7
Another way to convert timestamps to dates is by using the INT function to remove the fractional part of the timestamp’s date value. This effectively removes the time values from the timestamp.
After converting our timestamp into an integer, we can display it as a date using the TO_DATE function.
Summary
This guide should be everything you need to convert a timestamp value into a date in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.