Your spreadsheet shows 1728734400 where a date should be?

You exported your orders, messages or activity history from an app, opened the file in Excel, and the date column is full of numbers like 1728734400. Some files have even longer ones, such as 1728734400000, with 13 digits. The file is not broken and nothing is lost: the date is there, just written in the counting format that computers use among themselves.

This guide shows you how to read that number: what it counts, the difference between 10 and 13 digits, what to watch for when converting it to your own time zone, and how to turn a whole column into readable dates in Excel with a single formula.

What is the number counting?

It counts the seconds since the very first moment of 1970. The starting point is 1 January 1970 at 00:00, defined in world time, UTC; in computing this moment is called the Unix epoch. 1728734400 says that a little over one billion seven hundred and twenty-eight million seconds have passed since then, which lands on 12 October 2024 at 12:00 UTC: 13:00 in London and 08:00 in New York on that day.

Why store it like this instead of writing a date? Because a single number ignores countries, languages and calendars. "12/10/2024" means 12 October in the UK and 10 December in the US, whereas 1728734400 points to the same instant everywhere. Working out how much time passed between two records also becomes a simple subtraction: divide the difference by 60 for minutes, by 3600 for hours and by 86400 for days.

A rule of thumb for quick estimates: one billion seconds falls on 9 September 2001 and two billion on 18 May 2033. So a 10-digit value starting with 1 lies between those two dates; records from 2020 to 2027 start with 16 or 17.

Count the digits first: 10 or 13?

The same moment can be written in two units. Systems that count in seconds give today's values 10 digits; those that count in milliseconds add three more digits, making 13. Convert without knowing which one you have and the result is nonsense: treat a 13-digit value as seconds and you land somewhere around the year 56,000; treat a 10-digit value as milliseconds and you end up in the third week of January 1970. If you see a strange year, this is the first suspect.

When a long number does not fit in its cell, Excel shortens it to something like 1.72873E+12, and in that form you cannot count digits. Double-click the line on the right edge of the column heading to widen the column, or select the cells and pick Number from the number format list on the Home tab, then reduce the decimal places to zero. Once the full value is visible, count the digits.

Two rarer cases exist too. Values with 16 digits are microseconds: delete the last three digits to get 13-digit milliseconds, or the last six to get 10-digit seconds. A 9-digit value is not broken either; it simply points to a date before September 2001.

Converting to your local time: mind daylight saving

The timestamp always counts in UTC; to get local time you add or subtract your offset from UTC. The catch is that in most of Europe and North America that offset changes twice a year. New York is UTC−4 in summer (EDT) and UTC−5 in winter (EST); London is UTC+1 in summer (BST) and the same as UTC in winter. Always use the offset that applied on the date of the record, not the one that applies today.

The Unix Timestamp Converter on this site offers two settings: UTC, and Turkish local time (UTC+3 today, with the old winter and summer rule applied to dates before 2016). Outside Turkey, choose UTC and apply your own offset to the result. The table shows how the same values read in UTC and in New York.

Turning a whole column into dates in Excel

If the values are in column A starting at A2, type the right formula from the list below into row 2 of an empty column, press Enter, then double-click the small square at the bottom-right corner of the cell to fill the formula down the whole column. The formulas give UTC; to show local time, add your offset in days, for example +1/24 for London in summer or -5/24 for New York in winter.

If the result looks like 45577.5, you have not done anything wrong: Excel stores dates as a count of days and the cell simply has no date format yet. Select the column, press Ctrl+1 to open Format Cells, and on the Number tab pick a format from the Date or Custom category that shows both date and time. The 86400 in the formula is the number of seconds in a day.

• 10 digits (seconds): =A2/86400+DATE(1970,1,1)
• 13 digits (milliseconds): =A2/86400000+DATE(1970,1,1)
• Mixed column: =IF(LEN(A2)>=13,A2/1000,A2)/86400+DATE(1970,1,1)
• The other way, from a UTC date in B2 back to a timestamp: =ROUND((B2-DATE(1970,1,1))*86400,0)
• The same formulas work in LibreOffice Calc; in Google Sheets the EPOCHTODATE function converts a timestamp straight into a UTC date

Checking a few values quickly

If you only want to know about one or two rows, you do not need a formula. Paste the values into the Unix Timestamp Converter, one per line; it looks at the digit count of each line and decides by itself whether it is seconds or milliseconds, so 10 and 13-digit values can be mixed. Pick UTC from the time zone list and apply your own offset.

The tool also works in reverse: type 2024-10-12 12:00 on a line and it returns the matching timestamp in seconds. That is handy when a system asks you for something like "records after this date" as a number.

Values the tool cannot read are marked next to the line. The usual ones are negative timestamps, which point to dates before 1970, and values longer than 14 digits; if you have microseconds, delete the last three digits and try again.

The year 2038 problem: does it affect you?

Some older systems store this number as a signed 32-bit integer. The largest value such a field can hold is 2147483647, which is 19 January 2038 at 03:14:07 UTC. One second later the number overflows, turns negative, and those systems read the date as 13 December 1901.

The practical upshot for you: a timestamp larger than 2147483647 is not broken, it simply points past 2038; most current systems keep time in 64 bits and are not bound by that limit. If, on the other hand, a record shows an unexpected date such as 1901, the software that produced it may have hit this limit, and it is worth reporting to whoever supplies it.

Frequently asked questions

Does a Unix timestamp show my local time?

No. It is always counted in UTC and carries no time zone information. To get local time, apply your offset from UTC for that date, remembering that daylight saving time changes the offset in summer.

My converted date says 1 January 1970, or 31 December 1969. Why?

The cell in the source file was empty or zero, and zero is the moment the count starts. In time zones behind UTC, such as the Americas, that moment falls on the evening of 31 December 1969. These rows hold no real date; the record was exported incomplete.

Is it fine to delete the last three digits of a 13-digit number?

Yes. Removing the last three digits of a 13-digit millisecond value turns it into 10-digit seconds; you only lose fractions of a second, which do not matter for a date and time. In Excel, dividing by 1000 gives the same result without editing the value.

How do I turn a date back into a timestamp in Excel?

If the UTC date is in B2, =ROUND((B2-DATE(1970,1,1))*86400,0) gives the timestamp in seconds. For a local date, subtract your offset first, for example B2+5/24 for a New York winter time. Multiply by 1000 if you need milliseconds.

My phone app's backup has the same kind of numbers. Does this method apply?

Yes. Date fields in the JSON and CSV files that apps export usually use the same counting system. Check the digit count to identify the unit, then convert with the Unix Timestamp Converter or the Excel formula.

Last updated: October 12, 2026