How to Extract Date From Time Stamp in Google Sheets

0
185
Extract Date From Time Stamp in Google Sheets

The Time Stamp keeps 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 time stamp in Google Sheets? I will explain it in detail and also you can get the tips to remove time from time stamp. I’ve three custom formulas to remove time from time stamp in Google Sheets. Now first to the logic.

Why to Extract Date From Time Stamp in Google Sheets

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

Example:

Why to Extract Date From Time Stamp in Google Sheets

Here as you can see, I’ve compared dates in Column H with Date in Column I. This example clearly portrays why it’s necessary to remove time form date in other words extract date from time stamp. When you compare a date with another same date but time stamp, it would return FALSE.

How to Extract Date From Time Stamp in Google Sheets

You can extract date from time stamp in different ways in Google Sheets. See that custom formulas below.

Here Cell A1 contains the time stamp.

Formula 1 Using TO_DATE and INT functions.

=TO_DATE(int(A1))

Formula 2 Using SPLIT function.

=SPLIT(A1,” “)

The difference here is it split the date and time in to two separate cells.

Formula 3 Using TO_DATE and DATEVALUE functions.

=TO_DATE(DATEVALUE(A1))

We have learned how to remove time from time stamp 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 time stamp. Instead of using our formula one above, you can directly use this formula. Here INT function returns date value that can easily compare with date. That’s all for now. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here