Extracting Date From Timestamp in Google Sheets: 5 Methods

In Google Sheets, extracting a date from a timestamp can be done using various functions. Here, we present the 5 easiest methods.

Learning this technique is crucial, especially when you need to look up a date in a timestamp column or use FILTER or QUERY to filter a timestamp column based on date criteria.

You can utilize one of the formulas below for this purpose, converting timestamps to dates and enabling their use in lookups or filters.

How to Extract a Date From a Timestamp in Google Sheets

For the examples, let’s consider the datetime (timestamp) in cell A1, which is “05/08/2023 13:50:29”. The formulas provided will work seamlessly, even if the timestamp contains milliseconds, such as “05/08/2023 13:50:29.231”.

Please note that the result of the formulas will be a date value. You need to format it as a date by navigating to the cell containing the formula (date value) and clicking “Format > Number > Date.” Alternatively, you can wrap the formula with the TO_DATE function.

Now, let’s explore the five formulas to extract a date from a timestamp:

Formula 1 Using INT Function

=INT(A1)

The INT function rounds down a value to the nearest integer, which applies to a timestamp. In a timestamp, the time component is treated as the fractional part.

Formula 2 Using TRUNC Function

=TRUNC(A1)

The TRUNC function uses ‘value’ and ‘places’ arguments. If ‘places’ are omitted, it truncates all decimal places, making it suitable for extracting dates from timestamps in Google Sheets.

Formula 3 Using the ROUNDDOWN Function

=ROUNDDOWN(A1)

You can effectively extract a date from a timestamp by rounding down a number to 0 places, which returns the whole number.

Formula 4 Using FLOOR Function

=FLOOR(A1)

The FLOOR function rounds a number down to a specified multiple. It requires two arguments: ‘value’ and ‘factor.’ If you omit the ‘factor,’ it defaults to 1. Therefore, the above formula is used to extract the date from the timestamp in cell A1.

Formula 5 Using the DATEVALUE Function

=DATEVALUE(A1)

This DATEVALUE formula has certain advantages over the previous ones. The datetime in cell A1 must be a recognized date format and can also be a date string. It ensures the return of the date component only. Unlike other formulas, if cell A1 contains a number instead of a timestamp, it will return an error.

Next, we will convert these formulas into array formulas to transform timestamps in a column into dates.

Transforming Timestamps in a Column into Dates

To transform timestamps in a column into dates, you can follow this generic formula for Formulas 1 to 4:

=ArrayFormula(IFERROR(IF(DATEVALUE(A1:A), your_formula,)))

Replace your_formula with the formula you prefer. For example, if you want to use the TRUNC function, replace your_formula with TRUNC(A1:A).

Example:

=ArrayFormula(IFERROR(IF(DATEVALUE(A1:A), TRUNC(A1:A),)))

The IF(DATEVALUE(A1:A) part tests if the provided range contains valid dates, then truncates the decimal parts; otherwise, it returns errors. The IFERROR is used to remove those errors.

"Extracting date from timestamp or datetime in Google Sheets using Array Formula

However, for the fifth formula, you can use the following formula:

=ArrayFormula(IFERROR(DATEVALUE(A1:A)))

Resources

We have 5 formulas to extract dates from timestamps in Google Sheets. Additionally, we have explored the array formula versions of these functions.

Here are some timestamp-related resources.

16 COMMENTS

grangthony May 18, 2023 At 6:50 am

Greetings,

Having some issues where trying to extract a date from a timestamp generated by merchant service device returns an incomplete date with formula.

Example:

Payment date | Extracted Date

30-Apr-2023 02:01 PM PDT | 30-Apr-202

Tried these 2 functions, and they both return the year cut-off like the example above when trying to extract.

1)

=MID(A1,FIND("-",A1,1)-2,10)

2)

=MID(A1,SEARCH("??-???-",A1),10)

Not sure what I’m doing wrong, but any help is greatly appreciated!

- Prashanth May 18, 2023 At 8:54 am
  
  Hi, grangthony,
  
  You can try this SPLIT + CHOOSECOLS combo.
  
  =choosecols(split(A1," "),1)
  
  Another option is REGEXEXTRACT, but the result will be text formatted.
  
  =REGEXEXTRACT(A1,"(\S+)")
  
Manikandan Selvaraj July 27, 2022 At 11:01 am

Hi Prashanth,

I am using the IFS function for automatic date filling in Google Sheets.

=IFS(AB1="","",AC1="",NOW(),TRUE,AC1)

But the formula is showing yesterday’s date, not today’s date.

- Prashanth July 27, 2022 At 5:25 pm
  
  Hi, Manikandan Selvaraj,
  
  It doesn’t make sense to me. Can you please explain the type of values in the relevant cells, i.e., in AB1 and AC1?
  
  Whether they are dates, numbers, or texts?
  
  - Manikandan Selvaraj July 27, 2022 At 5:45 pm
    
    Hi Prashanth,
    
    Sure, AB1 contains drop-down TEXTS.
    
    When the user changes the status text on the AB column, the AC column automatically captures the current date.
    
    But here the issue is it captures yesterday’s date, not the current date. For reference:
    
    AB | AC
    User Status | Status Date
    Completed | 25/06/2022
    Ongoing | 26/07/2022
    
    - Prashanth July 28, 2022 At 2:17 pm
      
      Hi, Manikandan Selvaraj,
      
      Please see if this help?
      
      =if(AB1="Completed",today()-1,today())
      
Phil August 20, 2020 At 1:52 am

I want to use a Vlookup for the date only in a timestamp, can I embed something in the formula that only reads the date or do I need a helper column that uses =TO_DATE(INT(A1))?

- Prashanth August 21, 2020 At 8:42 am
  
  Hi, Phil,
  
  I’ll post an example soon and notify you here!
  
- Prashanth August 28, 2020 At 4:31 pm
  
  Hi, Phil,
  
  Here is the post link – Vlookup Date in Timestamp Column.
  
Hima Bindu March 13, 2020 At 1:52 pm

Thank you so much 🙂

Hima Bindu March 11, 2020 At 7:08 pm

Is there a way to get the result in two columns? In one column date and in another column text.
12-Jan-20 Brazil
5/6/2020 Canada

Like above?

I just want to split the dates in one column and strings in the next column.

- Prashanth March 11, 2020 At 9:33 pm
  
  Maybe this one?
  
  =ArrayFormula(TO_DATE(IFERROR(split(A1:A," "))))
  
  If not working, you can share a demo sheet as earlier.
  
Hima Bindu March 11, 2020 At 6:52 pm

Hi,

How do I get only dates from the list of a column?

Example: in ColumnA I have;

Aus
CAN
1/2/2019
UNI
5/6/2020

In the next column, I should get only 1/2/2019 & 5/6/2020. Is there any way to extract only dates? Please help me.

- Prashanth March 11, 2020 At 9:23 pm
  
  Hi, Hima,
  
  Try the below Filter + Datevalue combo.
  
  =Filter(A1:A,datevalue(A1:A))
  
  The function DATEVALUE normally requires ARRAYFORMULA in a range (List). But when using within Filter, it’s not required.
  
Ahmet Bacak August 15, 2019 At 3:20 pm

Thank you so much. I was sick because of this problem. All the time I was adding another column next to the date and using =Left and =right functions. Your 3rd formula worked perfectly with =ARRAYFORMULA(TO_DATE(DATEVALUE((List1!A3:A)))

Thank you.

- Prashanth August 16, 2019 At 10:24 am
  
  Hi, Ahmet,
  
  Thanks for your feedback!