Excel Convert Time to Unix Timestamp: Formula Guide

Updated 4 November 2026 · Standard formula, shown below

To convert a time value in Excel to a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC), use the formula (A1 - DATE(1970,1,1)) * 86400, where A1 contains the date and time in Excel’s serial format. If your time is stored as text, you must first parse it with DATEVALUE and TIMEVALUE before applying the conversion.

What Is a Unix Timestamp?

A Unix timestamp is a single integer that counts the number of seconds elapsed since the Unix epoch: January 1, 1970, at 00:00:00 UTC. It ignores leap seconds, so it is not exactly equal to the true number of elapsed seconds, but it is the standard used by most programming languages, databases, and APIs. For example, 2024-01-01 00:00:00 UTC corresponds to 1704067200. Excel stores dates differently: as a serial number where 1 equals January 1, 1900, and the fractional part represents the time of day. This fundamental difference is why a direct conversion formula is needed.

How Excel Stores Dates and Times

Excel’s date system uses a serial number for each day. In the 1900 date system (the default on Windows), January 1, 1900 is serial 1, and January 1, 1970 is serial 25569. The time portion is a decimal fraction of a 24-hour day: 6:00 AM is 0.25, 12:00 PM is 0.5, and 6:00 PM is 0.75. A full date-time value like 2024-01-01 12:00 is stored as 45292.5. On Mac versions of Excel that use the 1904 date system, the base date is January 2, 1904, so the offset to the Unix epoch is different: you would use DATE(1904,1,1) instead of DATE(1970,1,1) in the formula, or add 1462 days to the Windows offset. To check which system your workbook uses, look at the date of serial 1 in a cell: if it shows 1900-01-01, you are on the 1900 system; if it shows 1904-01-02, you are on the 1904 system.

The Basic Conversion Formula

The core formula is simple: subtract the Unix epoch from your date-time, then multiply by 86,400 (the number of seconds in a day). The subtraction gives the number of days between the two dates, and the multiplication converts days to seconds.

If your date-time is in cell A1 (as a real Excel date-time), use: =(A1 - DATE(1970,1,1)) * 86400
If your date-time is in UTC, the result is the Unix timestamp directly.
If your date-time is in a local time zone, you must adjust for the offset from UTC. For example, if your time is Eastern Standard Time (UTC-5), add 5 hours (18000 seconds) to the result, or subtract the offset in the formula: =((A1 - DATE(1970,1,1)) * 86400) + 18000.

For a date only (no time), the formula still works because the time portion is 0, giving the timestamp for midnight UTC of that date.

Handling Text Dates and Times

If your data is stored as text (e.g., “2024-01-01 12:00:00”), you need to convert it to a real date-time first. Excel’s DATEVALUE function parses a date text string, and TIMEVALUE parses a time text string. Combine them and then apply the conversion:

Assume the text is in cell A1.
Use DATEVALUE(LEFT(A1,10)) to get the date part (assuming format YYYY-MM-DD).
Use TIMEVALUE(RIGHT(A1,8)) to get the time part (assuming format HH:MM:SS).
Add them: DATEVALUE(LEFT(A1,10)) + TIMEVALUE(RIGHT(A1,8)) gives the full date-time serial.
Then apply the Unix conversion: =((DATEVALUE(LEFT(A1,10)) + TIMEVALUE(RIGHT(A1,8))) - DATE(1970,1,1)) * 86400

If your text format is different, adjust the LEFT and RIGHT functions or use MID to extract the correct parts. Excel’s DATEVALUE may fail on some regional formats, so you might need to use DATE with YEAR, MONTH, and DAY extracted from the text.

Converting Unix Timestamps Back to Excel Dates

The reverse conversion is equally useful. To turn a Unix timestamp back into an Excel date-time, divide by 86,400 and add the epoch date:

If the timestamp is in cell B1, use: =B1 / 86400 + DATE(1970,1,1)
Format the result as a date and time in Excel (e.g., yyyy-mm-dd hh:mm:ss).
Remember that the result is in UTC. If you need a local time, add or subtract your time zone offset in hours divided by 24 (since Excel’s day unit is one day). For example, for UTC+2, add 2/24.

This is handy when you receive timestamps from a database or API and need to display them in a readable format.

Common Mistakes and Pitfalls

Wrong epoch date: Using DATE(1900,1,1) instead of DATE(1970,1,1) gives a result off by about 2.5 million seconds (roughly 29 years). Always use 1970.
Forgetting the time zone: If your Excel time is local, the formula gives a timestamp that is off by your UTC offset. Convert to UTC first or adjust the result.
Using the 1904 date system: If your workbook uses the 1904 system, the epoch offset is different. Use DATE(1904,1,1) or add 1462 days to the 1970 offset.
Rounding errors: Excel stores times as floating-point numbers, so the result may have a tiny fractional part. Use ROUND to get an integer if needed: =ROUND((A1 - DATE(1970,1,1)) * 86400, 0).
Text dates not parsed: If you see a #VALUE! error, the text is not recognized as a date by Excel’s locale. Use explicit date parsing with DATE and TIME functions.

Alternatives to Manual Formulas

If you prefer not to write formulas, you can use Excel’s built-in Power Query (Get & Transform) to convert dates to Unix timestamps. In Power Query, you can add a custom column with the formula Duration.TotalSeconds([YourDate] - #datetime(1970,1,1,0,0,0)). This is especially useful for large datasets because it avoids dragging formulas down thousands of rows. Another alternative is to use a VBA macro that loops through cells and applies the conversion, but the formula method is simpler and more transparent for most users.

Frequently Asked Questions

Why does my Unix timestamp show a negative number?

A negative timestamp occurs when the date is before January 1, 1970. Unix timestamps are defined as seconds since that date, so any earlier date yields a negative value. This is valid in most systems, but some APIs may not accept negative timestamps. If you need to work with pre-1970 dates, consider using a different epoch or a signed 64-bit integer.

How do I convert a Unix timestamp to a readable date in Excel?

Divide the timestamp by 86400 and add DATE(1970,1,1). For example, =A1/86400 + DATE(1970,1,1) where A1 contains the timestamp. Then format the cell as a date and time. The result will be in UTC; adjust for your time zone if needed.

Does Excel’s date system affect the conversion?

Yes. The default 1900 date system works with the formula given. If your workbook uses the 1904 date system (common on older Mac files), you must add 1462 days to the epoch offset. You can check by entering =DATE(1900,1,1) in a cell: if it shows 1900-01-01, you are on the 1900 system; if it shows 1904-01-02, you are on the 1904 system.

Can I convert a time-only value to a Unix timestamp?

Yes, but you need to specify a date. A time-only value in Excel is a fraction of a day, so it represents a time on January 0, 1900 (or 1904). To convert, add a date to it first, such as today’s date: =((TODAY() + A1) - DATE(1970,1,1)) * 86400. This gives the timestamp for that time on today’s date.

What is the difference between Unix timestamp and Excel serial date?

Unix timestamps count seconds since 1970-01-01 00:00:00 UTC, while Excel serial dates count days since 1900-01-01 (or 1904-01-02). The conversion requires multiplying by 86400 and adjusting the epoch. The two systems are not interchangeable without conversion.

How do I handle milliseconds in a Unix timestamp?

Many systems use milliseconds since the epoch. To convert from milliseconds to Excel date, divide by 86400000 (1000 * 86400) and add the epoch. To convert from Excel to milliseconds, multiply by 86400000. The formula is the same but with a different multiplier.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.