Google Sheets: Extract a Date from a Timestamp

To extract the date from a timestamp in Google Sheets, use =INT(A2) when A2 contains a real date-time value, then format the result as a date with Format > Number > Date. Google Sheets stores dates and times as numbers, so INT removes the fractional time portion while keeping the calendar date.

What a timestamp contains in Google Sheets

A Google Sheets date-time value combines a date and a time in one serial number. The whole-number part represents the date, and the decimal part represents the fraction of a 24-hour day that has passed. For example, 0.5 represents noon, while a value with a whole-number part and a decimal part represents a date at a particular time.

This is why a timestamp can look like one piece of text in a cell but still be easy to split when Sheets recognizes it as a date-time value. The displayed appearance depends on the cell’s number format: the underlying value may contain hours and minutes even when the cell shows only a date. Conversely, a cell may look like a timestamp but contain plain text, which cannot be separated using INT in the same way.

The distinction matters because a date extracted with INT remains a numeric date value. You can sort it chronologically, use it in date calculations, or group records by day. A formula using TEXT can also display the date, but its result is text and is less useful for calculations.

Extract the date from a real date-time value

Suppose A2 contains a recognized timestamp such as March 8, 2025, at 2:35 PM. With the timestamp in A2, enter =INT(A2) in B2. The result is the date value for March 8, 2025, with the time removed. To show it as a familiar date, select B2 and choose Format > Number > Date.

If the output appears as a number instead of a date, the extraction may still have worked. That number is the date’s serial value; change the number format to Date to display it as a calendar date. You can also copy the formula down the column using the fill handle, or select the formula cell and copy it into the rows below.

If you want the formula itself to return a date-formatted value, with the timestamp in A2, use =TO_DATE(INT(A2)). TO_DATE converts the number to a date value, but the cell’s display format still controls how that value looks. Applying Format > Number > Date is the dependable way to choose the visible date style.

For a column of values, with timestamps in A2:A and blank rows possible, use =ARRAYFORMULA(IF(A2:A="","",INT(A2:A))) in B2. It fills the results down automatically and leaves output blank where the source cell is blank. Format the output column as a date. If your data may contain text or errors mixed in with numeric timestamps, check those rows separately; the array formula does not automatically repair malformed source values.

Format the result without changing the value

Formatting and extracting are different operations. A custom date-time format can hide the time while leaving it in the cell. For instance, selecting a date-time cell and choosing Format > Number > Date changes what you see, but a formula that refers to that cell can still use the original time component. Use INT when you need the time removed from the value itself.

For a specific display style, select the output cells and choose Format > Number > Custom date and time. You can then select or enter a date pattern, such as a year-month-day style, depending on the controls available in the dialog. This changes presentation, not the underlying extracted date. A spreadsheet’s locale can also affect default date order and separators, so a date that appears month-first in one sheet may appear day-first in another.

If the result is going into a report or needs a fixed text appearance, with the date value in B2, use =TEXT(B2,"yyyy-mm-dd") to return a string such as 2025-03-08. Choose this when you specifically need text for a label or export format. For ordinary date sorting, filtering, and calculations, keep the result as a date value instead.

If the timestamp is stored as text

INT works on numeric date-time values, not arbitrary text. If you see an error, or if changing the number format does not affect the cell, check whether the timestamp is text. A common clue is an imported value that includes a literal T or a time-zone suffix, such as 2025-03-08T14:35:20Z. The exact conversion depends on the text format and the spreadsheet’s locale.

For a consistently formatted ISO-style timestamp whose first ten characters are YYYY-MM-DD, with that text in A2, use =DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(MID(A2,9,2))). This reads the year, month, and day directly and returns a date value. It does not convert the time or apply the Z suffix as a time-zone adjustment; it extracts the calendar date written at the start of the text.

For text that begins with a date in a format Sheets recognizes, DATEVALUE may be simpler. With a text date in A2, use =DATEVALUE(A2). DATEVALUE parses a date string into a date value, but accepted text formats can depend on the spreadsheet locale. Test the result on representative rows before filling it through a large imported dataset, especially if dates could be interpreted as either month/day/year or day/month/year.

If the text uses a different pattern, avoid assuming that every timestamp can be parsed the same way. A value such as 03/08/2025 14:35 is ambiguous without knowing whether the first number means a month or a day. Confirm the source system’s format, then choose a parser that matches it. For a small dataset, correcting the import settings or normalizing the source format may be safer than building a formula around inconsistent strings.

Convert Unix timestamps

Some systems store timestamps as Unix time: a count of seconds or milliseconds since January 1, 1970, rather than a Sheets date-time serial. Applying INT directly to that number will not return the intended calendar date. First convert the count to a Sheets serial value, then take its whole-number date portion.

For a Unix timestamp in seconds in A2, use =TO_DATE(INT(A2/86400+DATE(1970,1,1))). For example, if A2 contains 1741444520, the UTC instant is March 8, 2025, at 14:35:20, and the formula returns the date March 8, 2025. There are 86,400 seconds in a day, which is why the formula divides by 86,400 before adding the Unix starting date.

If the source count is in milliseconds, with milliseconds in A2, use =TO_DATE(INT(A2/86400000+DATE(1970,1,1))). The divisor is 86,400,000 because there are that many milliseconds in a day. Check whether the source uses seconds or milliseconds before converting: confusing the two produces a date far from the intended one.

Time zones and date changes

A timestamp identifies a point in time, but its calendar date can differ depending on the time zone used to display it. For example, an instant shortly after midnight UTC may still be the previous evening in a location several hours behind UTC. Extracting the first ten characters of an ISO text value ending in Z gives the UTC date written in that text; it does not necessarily give the date for a local business location.

For a spreadsheet’s time-zone setting, open File > Settings and review the Time zone option. This setting is important when interpreting or entering date-time data, but it does not make every imported text string a fully converted time-zone-aware value. Decide whether reports should use UTC or a particular local time zone, and make sure the source data and conversion method agree. If a local date is required, apply the appropriate time-zone conversion before extracting the date; a fixed-hour adjustment may be wrong for locations that observe daylight saving time.

Common problems and how to check them

The output is a number. The formula may have returned a valid date serial. Select the output and choose Format > Number > Date.
INT returns an error. The source may be text, blank, or contain a non-date value. Check the cell’s contents and use a parser suited to the source format.
The date is one day earlier or later than expected. Check whether the timestamp is UTC or local time and whether the intended date is based on a different time zone.
The day and month appear reversed. Review the spreadsheet locale and the format of the imported text. An ambiguous date string cannot be interpreted safely without knowing its intended order.
The formula shows the original time. Confirm that you used INT rather than only changing the source cell’s display format, then format the result cell as a date.
Some rows work and others fail. The column may mix numeric timestamps, text timestamps, blanks, or different text layouts. Separate or normalize the input types before applying one conversion formula to all rows.

When debugging, try the formula on one known value first. Compare the source’s displayed timestamp with the extracted date, and verify whether the source is a number or text. A consistent test value helps distinguish a formatting issue from a parsing or time-zone issue.

Which method should you choose?

Choose the method based on what is actually in the cell, not just on how it looks. For recognized Google Sheets date-time values, INT is the shortest and most practical choice. For a date stored as ISO-style text, extracting the year, month, and day and passing them to DATE is predictable when the layout is consistent. For other text formats, DATEVALUE can work if Sheets recognizes the string under the sheet’s locale. For Unix counts, convert the units and starting date first.

Use numeric dates when the result will support date arithmetic, sorting, filtering, or grouping. Use TEXT only when a text string is specifically needed. If a column contains mixed formats or time zones, first establish a consistent definition of the date you want—UTC date, local date, or date as written by the source—then convert the records accordingly. That decision often matters more than the choice between two formulas.

Excel also stores ordinary dates and times as numbers, so the basic idea of removing the fractional time with INT is similar there. However, the steps and behavior described here use Google Sheets in a web browser, including its menus and settings; do not assume that import parsing or time-zone handling is identical across spreadsheet programs.

Frequently Asked Questions

Does INT remove the time from a Google Sheets timestamp?
Yes, when the cell contains a numeric date-time value recognized by Sheets. INT returns the whole-number date portion. Format the result as a date so it displays as a calendar date.

Why does my extracted date show as a number?
Google Sheets stores dates as serial numbers. Select the result and choose Format > Number > Date; the formula may already be returning the correct date value.

Can I extract a date from a timestamp that is text?
Yes, but the formula must match the text layout. DATEVALUE can parse recognized date strings, while fixed-layout text such as YYYY-MM-DD can be split into year, month, and day and passed to DATE.

How do I extract the date from a timestamp ending in Z?
If it is ISO-style text, you can extract the YYYY-MM-DD prefix with a formula that reads those date components. That gives the UTC date written in the string; it does not automatically convert the instant to a local time zone.

How do I keep the date only for an entire column?
For numeric timestamps in A2:A, use an ARRAYFORMULA with INT in B2 and format column B as a date. If the source has mixed types or errors, clean or handle those rows before relying on one formula for the whole column.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.