How to Extract Date From Timestamp in Google Sheets

To extract date from timestamp in Google Sheets is useful if you have DateTime values in your sheet, and you would like only to use their date value. 

If a cell contains a combined date and time, you might want to extract just the date value. In this post, we will go through all the possible methods to cut off the time value from a timestamp.

You will see why is it wrong just to format the cell and how can you actually extract date from timestamp using the TO_DATE function together with INT or DATEVALUE.

You will also see how to use the SPLIT function to separate the timestamp into date and time.

Let’s jump right in.

The Timestamp and DateTime in Google Sheets

When you work with data, you may want to enter the current date or time near an item. This is called timestamping.

There are multiple ways to add timestamps in Google Sheets, and we will discuss them below.

The timestamps are DateTime values. It is a type of value in Google Sheets, among others like number, text, boolean, Date, or Time.

DateTime is a combined value storing both date and time. Depending on your regional settings, they can have a different format than in the examples, but it always includes the following details: year, month, day, hour, minute, and seconds.

⚠️ How to Add Timestamps in Google Sheets

1. You can add the date and time manually. In this case, you have to be aware of the locale settings, because every region has its own pattern for displaying date and time.
2. You can use a keyboard shortcut to add a timestamp: Ctrl+Alt+Shift+; (semicolon) to add the current date and time.
3. You can use a formula: =NOW() which returns the current date and time to a cell. It is essential that this formula be recalculated, and the result be renewed with every change made in the table.
4. Or you can add timestamps automatically using a script.

The Purpose of Extracting Date From Timestamp

There might be different reasons for working with existing timestamps in your sheets. Usually, we insert a timestamp in Google Sheets to mark when an activity (create, update, delete) was done.

When we want to work with these values, they are not always in the right format for our calculations.

Say you need only the date of a timestamp and not the time part because you would like to calculate something with the days of the timestamps.

In some cases, it might be enough to simply change the format of the cell by choosing Format > Number > Date.

But be careful!

As you can see, the result on the right (above image): only the day of the timestamp appears in the cell. However, it doesn’t mean that the time (hours, minutes, seconds) has been cut off from the timestamp.

It still exists just that it is not visible in the cell anymore.

Let’s prove it! We have two different timestamps on the same day. Then we format the values only to make the date visible. They look identical now, but are these two cells really identical?

We are checking it with a simple comparison using the EQ (equality) function.

=EQ(C4, C5)

The equality function returns FALSE, meaning that the two cells do NOT have the same value.

Therefore, if you use these cells in other calculations, the values they represent are still the original values with the time details. Any comparison based on days will fail because the hidden part of the timestamps still remains.

Anytime you need your values to contain only the date of your timestamp and not the time, you will need to extract the date from the timestamp.

In the next part, we will show some methods to do that.

You may make a copy of the spreadsheet using the link attached below and try it for yourself:

How to Extract Date From Timestamp in Google Sheets

There are different ways to clean the time from timestamps and only keep the date. Let’s go over each one:

Method 1: Convert DateTime to Date using TO_DATE

An easy way to convert the DateTime to Date is by using the TO_DATE function.

The TO_DATE function converts a provided number to date. It accepts one cell with a number to be converted to a date.

We don’t have a number in our timestamp, but a DateTime, so what can we do?

It is good to know that Google Sheets treats every Date and DateTime as numbers in the background. The date is stored as an integer, and the time is stored as a decimal fraction.

You can easily check how it works by formatting any of your Date or DateTime cells to numbers. For example, our first timestamp from the example is stored as the number 43912.420775463.

When we would like to convert our DateTime to a Date using the TO_DATE function, we need to convert a non-integer to an integer in the background.

There are two different ways to do this.

• One way is to use the INT function that will simply cut off the decimal part of the number. We can use this formula to extract date from timestamp using TO_DATE and INT functions:

=TO_DATE(INT(A1))

• The other way is to use the TO_DATE function together with the DATEVALUE function. The DATEVALUE function converts a string to a date object. We treat our DateTime value as a string and use this function to create a date from it. The formula with TO_DATE and DATEVALUE will look similar to the previous solution:

=TO_DATE(DATEVALUE(A1))

Method 2: Split the timestamp into date and time using SPLIT

The second way is a bit more sophisticated than the previous one, and it is a different approach. The SPLIT function splits the date and time into two separate cells.

It is useful if we want to use both the date and the time.

The SPLIT function divides text around a specified character or string and puts each fragment into a separate cell in the row.

Let’s look at the syntax of the SPLIT function:

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

Let’s break this down and learn what each of these terms means:

• = the equal sign ishow we start every function in Google Sheets.
• SPLIT this is our function. We will have to add the corresponding value(s) into it for it to work.
• text is the text to divide or the cell with the text to divide.
• delimiter is the character or characters to use to split text.
• split_by_each is an optional argument and it is TRUE by default. By default, each character of the delimiter is considered individually, e.g., if the delimiter is “the”, then the text is divided around the characters “t”, “h”, and “e”. Set split_by_each to FALSE to turn off this behavior.
• remove_empty_text is an optional argument, TRUE by default. It describes whether or not to remove empty results from the split results. The default behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty cell values are added between consecutive delimiters.

In our example, the text will be the cell containing the timestamp.

To split our timestamp into date and time, we need to specify the second argument which is the delimiter. We need to find the character(s) that separate the two parts of the timestamp. It is a white space (blank) character, so this is what we are going to use as a delimiter.

We are not adding the last two optional parameters of the SPLIT function, because we don’t want to change their default values.

So this is the formula to split the timestamp into date and time:

=SPLIT(A1," ")

The result is two separated cells, one with the date and another one with the time. Using the cell with the date, we have an extracted date from timestamp, which was our main goal.

That’s pretty much it. You can now extract date from timestamp in Google Sheets. Try experimenting and integrating the other numerous Google Sheets formulas, to create even more powerful formulas that can make your life much easier. 🙂

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe