Google Sheets Unix Timestamp to Date Conversion Guide

To convert a Unix timestamp in Google Sheets, divide seconds by 86,400 or milliseconds by 86,400,000, then add the result to the Unix epoch date, January 1, 1970. For a timestamp in seconds in cell A2, enter =DATE(1970,1,1)+A2/86400, then format the result as a date or date and time.

What a Unix timestamp represents

A Unix timestamp counts elapsed time from the Unix epoch: January 1, 1970 at 00:00:00 UTC. It is usually stored as a number, not as a calendar date. Many systems express the count in seconds, while others use milliseconds. The unit matters: dividing a millisecond value as if it were seconds produces a date thousands of years away from the intended result.

Google Sheets stores dates and times as serial numbers. The whole-number portion represents days, and the fractional portion represents part of a day. For example, one hour is 1/24 of a day. Conversion works by translating the timestamp into a number of days and adding those days to the epoch date. The resulting serial number can then be displayed as a familiar calendar date and time.

There is no need to install an add-on for ordinary Unix timestamp conversion. A short date-arithmetic formula is usually the clearest option, and it keeps the result usable for sorting, date calculations, and charts.

Convert a timestamp in seconds

Assume A2 contains a Unix timestamp measured in seconds. Enter this formula in B2:

With the timestamp in seconds in A2, use this formula in B2: =DATE(1970,1,1)+A2/86400

The divisor is 86,400 because there are 60 seconds in a minute, 60 minutes in an hour, and 24 hours in a day. For example, the value 1700000000 converts to November 14, 2023 at 22:13:20 UTC. The date may initially appear as a decimal number; that is the underlying Sheets serial value, not a failed conversion.

To convert an entire column, place the formula in the first result row and fill it down. You can drag the small square at the bottom-right corner of the selected cell, or copy and paste the formula into the rows below. A2 will update to A3, A4, and so on because it is a relative reference.

Convert a timestamp in milliseconds

Some APIs, exports, and application logs provide milliseconds since the epoch. A 13-digit value is a common clue, but digit count alone is not a reliable way to identify the unit for every dataset. Check the source documentation when possible. If A2 contains milliseconds, use:

With the timestamp in milliseconds in A2, enter this formula in B2: =DATE(1970,1,1)+A2/86400000

There are 86,400,000 milliseconds in a day. For example, 1700000000000 converts to the same instant as 1700000000 seconds: November 14, 2023 at 22:13:20 UTC. The two examples differ by a factor of 1,000, not by a different date system.

If a column mixes seconds and milliseconds, do not apply one divisor to every row without first checking the data. Mixing units can create dates that look plausible but are wrong. It is safer to standardize the source column or add a clearly documented rule that identifies the unit for each row.

Format the result as a date and time

After entering the formula, select the result cells and use Format > Number > Date time to show both the calendar date and time. Choose Format > Number > Date if you only need the date. Formatting changes how the serial number appears; it does not change the underlying converted value.

For a custom display, select Format > Number > Custom date and time and choose or arrange the date and time components. A format that includes hours, minutes, and seconds is useful when checking a conversion. If the source timestamps include fractional seconds, the displayed format may hide them unless you select a suitable custom format and the stored value retains that precision.

Use a date-only format for reports where the time is irrelevant.
Use a date-and-time format to inspect the full instant represented by the timestamp.
Keep the converted cells numeric if you need to sort chronologically or calculate time differences.

Formatting is not the same as converting a value to text. A formatted date remains a numeric date serial that Sheets can use in calculations. A formula such as TEXT can produce a text string instead; that can be useful for labels, but text is less convenient for date arithmetic and chronological sorting.

Understand UTC and local time

Unix timestamps represent instants relative to UTC. The conversion formula produces the UTC date and time represented by the timestamp. A common source of confusion is expecting the formula to automatically show the time in the viewer’s local time zone. Do not assume that it does: the formula is date arithmetic, and a time zone is a separate interpretation or adjustment.

A timestamp close to midnight UTC can fall on the previous or next calendar day in another time zone. For instance, a UTC time shortly after midnight may still be the previous evening in a western time zone. If a report must show local civil time, confirm which time zone the data is intended to use and how the spreadsheet’s time-zone setting is configured. In Google Sheets, the spreadsheet time zone is available under File > Settings.

A fixed offset can be applied for a specific calculation, but it is not a robust replacement for a named time zone. Many places change their UTC offset seasonally, and the date when that change occurs can vary. A fixed adjustment such as adding or subtracting a certain number of hours will not account for daylight-saving transitions. For records that must follow local civil time across the year, use a workflow that explicitly handles the relevant time-zone rules rather than relying on one constant offset.

Convert a column and preserve the original data

For a one-time conversion, place formulas in a neighboring column and leave the original timestamps intact. This makes it easy to compare a converted date with its source value, find rows using the wrong unit, and correct mistakes without losing the input data. Add a clear header such as UTC date and time so other people know what the result represents.

If you need a fixed snapshot rather than live formulas, copy the converted cells and use Edit > Paste special > Values only in the destination. The pasted values remain dates, but they no longer update if the source timestamps change. Keep the original timestamp column if you may need to audit or redo the conversion.

For a large dataset, consider whether every row needs an individual formula or whether the conversion belongs in the data-import process. A helper column is transparent and easy to inspect; converting upstream can be more efficient when the same dataset is repeatedly imported. In either case, retain the source unit and time-zone meaning in column names or documentation.

Common problems and how to fix them

The result is a decimal number. The conversion may be correct, but the cell is formatted as a number. Select a date or date-and-time format using Format > Number.
The result is far in the future. A millisecond timestamp may have been divided by 86,400 instead of 86,400,000. Check the source unit and use the matching formula.
The result is near 1970. The input may be zero, a small timestamp, or a value that was imported incorrectly. Check whether the source uses seconds, milliseconds, or text that includes extra characters.
The displayed day differs from another system. Compare the time zones. The timestamp identifies a UTC instant, while the other system may display local time.
The formula returns an error or an unexpected date. Check that the cell contains a numeric timestamp. If the data arrived as text, remove unwanted spaces or characters and convert it to a number before doing the date arithmetic.
Seconds disappear from the display. The cell format may show only hours and minutes. Choose a date-and-time format that includes seconds if you need to inspect them.

Negative timestamps are also possible: they represent instants before January 1, 1970. Date serial support and display behavior can be less straightforward for dates before the spreadsheet’s supported range, so test such records carefully rather than assuming every negative value will display as expected.

Precision, sorting, and date calculations

Google Sheets represents dates as numeric values, so a converted timestamp can be compared, sorted, and used in calculations. Sort the converted date-time column in ascending order to put earlier instants first. If you sort only one column while leaving related data behind, however, the rows can become mismatched; select the full data range when sorting a table.

Seconds-based timestamps represent whole seconds, but millisecond sources can contain finer precision. Spreadsheet numbers use finite precision, and the visible format may round or hide small fractions. Avoid judging accuracy only by the displayed digits. If exact millisecond preservation is essential, retain the original integer timestamp as the authoritative value and use the converted date primarily for reading and analysis.

You can also subtract two converted date-time values to find elapsed days, then multiply by 24 for hours or by 86,400 for seconds. Ensure both values use the same unit interpretation and time-zone basis before treating the difference as meaningful. A timestamp is an instant; a displayed local clock time is a presentation of that instant.

How this differs from Excel

The core arithmetic is the same in Google Sheets and Excel: both can represent dates as serial values, so dividing by the number of seconds or milliseconds in a day and adding the epoch date works in either program. This page uses Google Sheets menus and behavior; in Excel, the formatting controls and menu labels can differ by platform and version.

Google Sheets formulas use commas between function arguments in the examples above. Spreadsheet locale settings can affect formula separators and date display conventions, so a sheet configured for a locale that uses semicolons may require different separators. The result’s displayed order can also vary by locale—for example, month-day-year versus day-month-year—without changing the underlying instant.

Frequently Asked Questions

What formula converts Unix seconds to a date in Google Sheets?
With the seconds timestamp in A2, use =DATE(1970,1,1)+A2/86400, then format the result as a date or date and time.

How do I convert milliseconds instead of seconds?
With milliseconds in A2, use =DATE(1970,1,1)+A2/86400000. The larger divisor accounts for the 1,000 milliseconds in each second.

Why does my converted timestamp show as a number?
Sheets stores dates and times as serial numbers. Select the result and choose Format > Number > Date time or another date format to display it as a calendar value.

Does the formula automatically convert UTC to my time zone?
The formula converts the timestamp to its UTC date and time. Local display can require a time-zone adjustment, and daylight-saving rules mean a fixed offset may not be correct year-round.

Can I convert a whole column at once?
Enter the appropriate formula beside the first timestamp, then fill it down for the other rows. Check that every row uses the same unit, or handle seconds and milliseconds separately.

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.