How to Convert Timestamp to Date in Google Sheets

When accuracy and efficiency are paramount, the following formulas offer the quickest and most reliable ways within Google Sheets to convert a mixed date-and-time timestamp into a clean, date-only value. We will proceed by detailing the mechanics of each approach, ensuring you understand why they are effective in stripping the time component.

Understanding Timestamps and Google Sheets Date System

To effectively manipulate time data in a spreadsheet, it is critical to grasp how Google Sheets interprets and stores dates. Unlike text formats, dates and times are stored internally as simple numbers—the serial date system. A whole number (like 44800) represents a specific day, and any decimal fraction following that number (like .5) represents the time of day (in this case, noon). For example, a value of 44800.75 means 75% through that specific day. A raw timestamp often contains this floating-point structure when generated by data entry or system processing, making it necessary to isolate the integer portion to obtain the pure date.

The fundamental goal of converting a timestamp to a date is therefore numerical truncation. If the input data is a standard serial number (days since 12/30/1899), removing the decimal part immediately yields the integer corresponding to the date. However, simply formatting the cell might not always work, especially if the data was imported as a text string or if it represents an epoch timestamp (seconds or milliseconds since 1/1/1970). The formulas presented below handle the necessary coercion and truncation, ensuring the final output is recognized by Sheets exclusively as a date value, ready for filtering and calculation.

It is vital to distinguish between a standard Sheets serial date/time number and a Unix epoch timestamp. While the methods detailed below are optimized for handling the standard serial date format commonly produced by Sheets’ own internal logging, handling a Unix timestamp would require an additional step involving division (e.g., dividing seconds by 86400 to convert to days, and then adding the date epoch offset) before applying the date extraction logic. For most in-sheet operations where a date and time are present, the serial number extraction techniques using DATEVALUE function or INT function are the appropriate tools.

Method 1: Utilizing the DATEVALUE Function for Conversion

The DATEVALUE function is primarily designed to convert a date represented as a text string into its numerical serial value within Google Sheets. While a standard serial timestamp is already numerical, wrapping it in `DATEVALUE` often forces Sheets to interpret the input string explicitly as a date entity before conversion, which can be useful when dealing with ambiguous data types. When a cell contains both date and time, `DATEVALUE` often focuses on the date part, particularly when the cell content is coerced into a text representation momentarily.

However, because timestamps can be complex, we pair `DATEVALUE` with the `TO_DATE` function. The `TO_DATE` function ensures that the resulting serial number—the output of `DATEVALUE`—is correctly recognized and displayed in a date format, regardless of any residual time components that might complicate simple formatting attempts. This combination is robust, explicitly enforcing the desired data type conversion from a potential text or mixed-format input into a clean numerical date output.

Deep Dive into Formula 1: DATEVALUE in Action

Formula 1 leverages the `DATEVALUE` function to extract the date component and then uses the `TO_DATE` wrapper to guarantee the output is formatted correctly. This method is highly effective for ensuring data consistency, particularly when the source data might occasionally shift between numerical and text formats. The logic isolates the integer component inherent in the date representation, discarding any associated time.

The structure begins with DATEVALUE function applied to the source cell, in this case, A1. If A1 contains a standard serial date and time (e.g., 44800.65), the function’s behavior is to return the numerical representation of the date it successfully parses. The surrounding `TO_DATE` function then instructs Google Sheets to display this numerical output using the default date format, ensuring the time component is completely absent from the resultant cell.

Formula 1: Use DATEVALUE

=TO_DATE(DATEVALUE(A1))

Both formulas convert the timestamp in cell A1 to a date.

Example 1: Convert Timestamp to Date Using DATEVALUE

The following demonstration illustrates the practical application of the `DATEVALUE` approach. Column A contains the original timestamp values, which include both date and time components stored as a single serial number. By applying the formula in Column B, we successfully isolate and display only the date portion.

The screenshot below confirms how utilizing the DATEVALUE function within the `TO_DATE` framework effectively converts the timestamp values found in column A into corresponding date values displayed in column B. This visual representation clearly shows the desired outcome: a clean separation of the date from the time.

Notice that only the date is shown in column B and the time has been removed. This is achieved because the DATEVALUE function, when working with serial dates, effectively returns the integer component of the serial number, which represents the date itself.

Method 2: Leveraging the INT Function for Time Removal

For numerical data already confirmed to be in the standard serial date system format—where the date is the integer and the time is the fraction—the INT function provides the most straightforward and often fastest conversion method. The `INT` function returns the integer part of a number by truncating the decimal portion. Since the decimal portion represents the time of day, this function is perfectly suited for date extraction.

When applied to a timestamp, `INT(A1)` strips away the hours, minutes, and seconds, leaving only the whole number that corresponds to the calendar date. We again wrap this calculation in the `TO_DATE` function. While `INT` returns the correct numerical value, `TO_DATE` is essential for instructing Google Sheets to apply date formatting to the output cell, ensuring it is visually presented as a date rather than a large serial number. This process is computationally light and highly reliable for standard Sheets serial inputs.

Deep Dive into Formula 2: INT Function Mechanics

Formula 2 is arguably the most direct way to convert a serial date/time into a date. It relies entirely on the mathematical structure of the serial date system. By applying the INT function, we guarantee that the fractional remainder of the timestamp is discarded. This ensures that the time of day is irrelevant to the output, regardless of whether it was 12:01 AM or 11:59 PM.

The syntax is simple: `INT(A1)` returns the integer serial number of the date. Then, `TO_DATE()` takes this integer and formats it. This is a crucial step because without `TO_DATE`, the cell would display a large number (e.g., 44800), which, while numerically correct, is not user-friendly. By using the combined formula, we efficiently perform the calculation and ensure proper presentation, isolating the timestamp‘s date component.

Formula 2: Use INT

=TO_DATE(INT(A1))

Example 2: Convert Timestamp to Date Using INT

Similar to the first example, this scenario demonstrates the effectiveness of the `INT` function in practical application. Column A holds the original timestamps, and Column B is populated using the `TO_DATE(INT(A1))` formula, resulting in a column of clean date values.

The following screenshot verifies how using the INT function yields the exact conversion results. This reinforces the concept that for standard serial date/time values in Sheets, numerical truncation is the key mechanism for isolating the date component.

Notice that only the date is shown in column B and the time has been removed. This confirms that the INT function successfully truncated the fractional part of the serial number, leaving only the integer date value.

Also notice that this formula returns the exact same results as the previous formula using `DATEVALUE`. This consistency highlights that both methods achieve the fundamental goal of date extraction from a standard serial timestamp.

Comparing DATEVALUE vs. INT: Efficiency and Practical Use

While both Formula 1 and Formula 2 achieve identical results when dealing with correctly formatted numerical serial timestamps, there are subtle differences in their intended purpose and general use cases. Understanding these differences allows for better formula selection depending on the specific source data.

The INT function method is generally preferred for its simplicity and numerical efficiency when you are certain the input cell (e.g., A1) contains a true serial date/time number (integer + fraction). It is a pure mathematical operation that is very fast, especially when dealing with extremely large datasets where calculation speed matters.

Conversely, the DATEVALUE function method is often more robust when the input data might occasionally be imported as a text string that visually resembles a date and time. Since DATEVALUE function is designed specifically for text-to-serial conversion, it handles data coercion more explicitly. In the context of clean numerical timestamps, however, it performs a function similar to INT, isolating the integer component necessary for the date. Regardless of the choice, the output is a serial date value that is subsequently formatted by `TO_DATE`.

Advanced Date Formatting and Customization

Once the timestamp has been successfully converted into a pure date serial number using either the `DATEVALUE` or `INT` method, you gain complete control over its display format. While `TO_DATE` applies the default date format of your spreadsheet’s locale, you may require a specific format, such as YYYY-MM-DD or Month/Day.

To apply custom formatting, follow these steps after the formula is in place:

1. Select the column or cells containing the newly converted dates (e.g., Column B).
2. Navigate to the top menu: Format > Number > Custom date and time.
3. Choose a predefined format, or define a unique format using pattern components (e.g., YYYY/MM/DD hh:mm:ss if you wanted time back, or simply DD-MMM-YY for date only).

This step is purely visual and does not alter the underlying serial date number produced by the formula. By mastering both the conversion (using `INT` or `DATEVALUE`) and the presentation (using custom formatting), you ensure your data is always accurate and displayed exactly as required for professional reporting and analysis.