Timestamp to Date in Google Sheets: Convert Unix Time

To convert a Unix timestamp in seconds to a date and time in Google Sheets, put the timestamp in A2 and enter =A2/86400+DATE(1970,1,1) in another cell. For a timestamp in milliseconds, divide by 86400000 instead; then format the result as a date or date and time.

Identify what kind of timestamp you have

A timestamp is a value that identifies a point in time, but the word can refer to different formats. The most common type in exported data, logs, and APIs is Unix time: a count of seconds or milliseconds since January 1, 1970 at 00:00:00 UTC. The number of digits is a useful clue, though not a guarantee. Current Unix timestamps in seconds are usually around 10 digits; timestamps in milliseconds are usually around 13 digits.

Before converting, check the source documentation or inspect a known record. A timestamp such as 1700000000 is seconds; 1700000000000 represents the same moment expressed in milliseconds. Dividing a millisecond value as if it were seconds produces a date far outside the intended range. Likewise, a 10-digit value divided as milliseconds lands near 1970 rather than the modern date you likely expect.

Not every timestamp is Unix time. Some systems provide a date as text, a formatted date and time, or a count of seconds from a different starting point. The formulas below assume the Unix epoch. Confirm the source’s unit and epoch whenever the output looks implausible.

Convert a Unix timestamp in seconds

Google Sheets stores dates as serial numbers: whole-number increments represent days, and the fractional part represents the time of day. A Unix timestamp counts seconds, so dividing by 86400 converts it to a number of days. Adding the date for the Unix epoch anchors that count to the calendar.

With a seconds-based Unix timestamp in A2, enter this in B2:

=A2/86400+DATE(1970,1,1)

For example, if A2 contains 1700000000, the result is November 14, 2023 at 22:13:20 when displayed as a date and time. The arithmetic adds 19,675 days and 22 hours, 13 minutes, and 20 seconds to the epoch. If B2 is shown only as a date, it displays November 14, 2023; the time remains in the cell value even though the format hides it.

To enter it, select B2, type the formula, and press Enter. If the timestamp is in a different column or row, replace A2 with that cell reference. The same calculation works for earlier dates, including negative Unix timestamps, which represent times before January 1, 1970.

Convert a timestamp in milliseconds

Milliseconds need a different divisor because there are 86,400,000 milliseconds in a day. With a millisecond timestamp in A2, use:

=A2/86400000+DATE(1970,1,1)

For example, 1700000000000 in A2 converts to the same moment as the seconds example: November 14, 2023 at 22:13:20. A common mistake is to divide this value by 86400. That treats each millisecond as a second and creates a date thousands of years away from the intended result.

If the source data mixes seconds and milliseconds in the same column, first identify which rows use which unit. Do not rely on a formula that guesses from digit count unless the data is known to follow a consistent rule. A mistaken classification can produce dates that appear valid but are wrong, which is harder to catch than an obvious error.

Format the result as a readable date

The conversion formula returns a numeric date-time value, not a text label. If the cell shows a number such as 45244.9259, the calculation may be correct; the cell is simply formatted as a number. Change the display format without changing the underlying value:

Select the converted cell or range.
Choose Format > Number > Date to show a date, or choose Format > Number > Date time to show both date and time.
For a specific display, choose Format > Number > Custom date and time, then select or build a pattern such as year-month-day and hour-minute-second.

A date-only format hides hours, minutes, and seconds; it does not remove them. For instance, the value may still be 22:13:20 even when the cell displays only 11/14/2023. To check the full value, temporarily apply a date-time format. The date order and appearance can vary with the spreadsheet’s locale, so 11/14/2023 may appear differently in a sheet configured for another region.

Understand UTC and time zones

Unix time describes an instant relative to UTC. The conversion formula calculates the UTC date and time represented by the timestamp, but the resulting cell does not carry a visible time-zone label. A value displayed as 22:13:20 should therefore be understood as the converted UTC time unless you deliberately adjust it for a local zone.

Google Sheets has a time-zone setting for the spreadsheet. In the web browser, open File > Settings and check the General section’s time zone. That setting matters for date-time functions and other spreadsheet behavior, but it does not make an arbitrary Unix timestamp formula a named-zone conversion tool. The arithmetic above turns the timestamp into a date serial; it does not automatically apply daylight-saving rules for a location such as New York or Paris.

If you need a fixed offset for a limited, well-defined case, add or subtract the offset in days. For example, with a UTC date-time value already calculated in B2 and a known local offset of five hours behind UTC, the illustrative adjustment is =B2-5/24. This assumes that the offset really is five hours for that date. It is not safe as a general conversion across regions or seasons because daylight-saving changes and time-zone rules can vary. For reporting that must follow a named location’s rules, use a system or data-processing step that explicitly handles time zones, and document whether the sheet contains UTC or local time.

Convert a whole column and preserve the source

For a list of timestamps, keep the original values in one column and put converted values in a neighboring column. With seconds-based timestamps in A2:A, enter the formula for A2 in B2, then fill it down for the rows that contain data. In Google Sheets, drag the small square at the lower-right corner of B2 down the column, or copy B2 and paste it into the target range. Relative references update from A2 to A3, A4, and so on.

For millisecond data, use the millisecond formula in the first result row and fill down in the same way. Keeping the source column is useful for auditing, troubleshooting, and redoing the conversion if you discover that the timestamps used a different unit. It also avoids replacing the only copy of data imported from another system.

If a timestamp is stored as text, the arithmetic may fail or behave unexpectedly. Check whether the cell is left-aligned or whether Sheets reports a formula error, but treat alignment only as a clue because formatting can affect it. With a text number in A2 that can be parsed using the sheet’s locale, this version converts it first:

=VALUE(A2)/86400+DATE(1970,1,1)

Use the corresponding 86400000 divisor for milliseconds. If VALUE returns an error, inspect the original text for spaces, separators, a leading apostrophe, or nonnumeric characters; clean or re-import the data rather than silently assuming the conversion succeeded.

Common errors and how to fix them

A large serial number appears: Apply a date or date-time format using Format > Number. The formula may already be correct.
The result is near 1970: Check whether a millisecond timestamp was divided by 86400, or whether a seconds timestamp was mistakenly divided by 86400000.
The date is far in the future: The divisor may be too small for the unit, or the source may not use the Unix epoch.
The displayed date is one day off: Check whether you are comparing a UTC date to a local calendar date. An instant close to midnight UTC can fall on the previous or next day in another time zone.
The formula returns an error: Check that the cell reference points to a numeric value. Text, blanks, labels, or extra characters may require cleaning before conversion.
Only a date appears, not the time: Change the number format to Date time or a custom format that includes hours, minutes, and seconds.

A useful check is to convert one timestamp whose expected date and time you already know, and compare the result before filling the formula down thousands of rows. Also verify whether the source records whole seconds or fractional seconds. Sheets can store fractional days, so dividing a timestamp with subsecond precision preserves the fraction mathematically, although the chosen display format may not show it.

How this compares with Excel and text dates

The two Unix conversion formulas also work in current desktop Excel because they use common functions and date serial arithmetic. The formatting menus differ: the steps above use Google Sheets’ web menus, while Excel uses its own number-format controls. If you exchange the workbook, confirm the cell’s date format and the spreadsheet or computer time-zone assumptions rather than assuming the displayed date proves the time zone.

A timestamp written as text in an ISO-style form, such as 2023-11-14T22:13:20Z, is not the same thing as a Unix number. The trailing Z indicates UTC in that convention. Do not divide such a string by 86400. It needs to be parsed as a date-time value using a method appropriate to the input format, or converted to Unix seconds by the system that supplied it. Text parsing can depend on separators, locale, and whether the time-zone marker is preserved, so verify the result against a known example.

For a clear, auditable spreadsheet, label columns with their meaning and unit, such as Timestamp (Unix seconds), UTC date-time, or Local date. These labels help prevent later users from treating a millisecond number as seconds or reading a UTC result as local time.

Frequently Asked Questions

How do I convert a 10-digit timestamp to a date in Google Sheets?
For a 10-digit Unix timestamp in seconds in A2, enter =A2/86400+DATE(1970,1,1), then format the result as Date or Date time. Confirm the source uses Unix seconds; digit count is only a clue.

How do I convert a 13-digit timestamp in Google Sheets?
A typical 13-digit Unix timestamp is in milliseconds. With the value in A2, use =A2/86400000+DATE(1970,1,1) and format the result. Check the source documentation if its unit is uncertain.

Why does Google Sheets show a number instead of a date?
Sheets stores dates as numbers. Select the result and choose Format > Number > Date or Date time to display it as a calendar value. Formatting changes what you see, not the calculated date serial.

Does the formula convert a timestamp to my local time?
No. It converts Unix time to the corresponding UTC date-time value. A local calendar time requires a correct time-zone conversion, including any daylight-saving rules; a fixed hours adjustment is reliable only when the applicable offset is known for that date.

Can I convert timestamps before 1970?
Yes. Unix timestamps before the epoch are negative, and the same seconds or milliseconds formulas can represent them. Make sure the source really uses Unix time and that the displayed date format supports the earlier date.

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.