How to Extract Date From Timestamp in Google Sheets

The Timestamp aka DateTime keeping the format similar to Google Sheets NOW time function. It’s like time suffixed to date as “21/01/2018 18:14:22”. Why it’s important to know how to extract date from the timestamp in Google Sheets?

I will explain it in detail and also you can get the tips to remove time from a timestamp. I’ve three custom formulas to remove time from the timestamp in Google Sheets. Now first to the logic.

The Purpose of Extracting Date From Timestamp in Google Sheets

If you make IF or IFS logical test in a range containing DateTime (timestamp) and normal date, the formula may return wrong results. Here is an example.

Example:

Here as you can see, I’ve compared dates in Column H with Date in Column I.

When you compare a date with another same date but timestamp, it would return FALSE.

This example clearly portrays why it’s necessary to remove time from the date in logical tests. In other words, it shows the importance of extracting date from a timestamp.

How to Extract Date From DateTime in Google Sheets

You can extract a date from DateTime in different ways in Google Sheets. See those custom formulas below.

Here are my examples, in that the cell A1 contains the timestamp.

Formula 1 to Convert DateTime to Date Using TO_DATE and INT Functions

=TO_DATE(INT(A1))

Are you new to INT in Sheets? I have included this function usage in my post here – How to Use Google Sheets TRUNC Function [Difference with INT and ROUND].

Regarding To_Date or any other date functions available in Sheets, please see this post – How to Utilise Google Sheets Date Functions [Complete Guide].

Formula 2 to Extract Date and Time from Timestamp Using SPLIT Function

=SPLIT(A1,” “)

The difference here is it splits the date and time into two separate cells.

Formula 3 To Convert DateTime to Date Using TO_DATE and DATEVALUE Functions

=TO_DATE(DATEVALUE(A1))

We have learned how to remove time from the timestamp in Google Sheets. Now how to use this in logical tests?

=IF(H1=INT(I1),TRUE,FALSE)

This formula is in line with the above screenshot. In that, in I1 you can see the timestamp. Instead of using our formula one above, you can directly use this formula.

Here INT function returns date value that can easily compare with the date. You can use any of the above formulas to extract date from a timestamp in Google Sheets. That’s all for now. Enjoy!

Related: