Convert Excel Time to Unix Time: Step-by-Step
- Excel datetime
- 45000.5
- You entered
To convert an Excel time value (a date and time stored as a serial number) to Unix time (seconds since January 1, 1970, 00:00:00 UTC), you subtract the Excel serial number for that epoch date and multiply by 86,400. The exact formula is =(A1 - DATE(1970,1,1)) * 86400, where A1 contains the Excel datetime. This guide explains the math, handles time zones, avoids common pitfalls, and provides ready-to-use formulas for both Windows and Mac Excel.
Formula
Excel stores dates as serial numbers: day 1 is January 1, 1900, and each whole number adds one day. Time is a fractional part (e.g., 0.5 = noon). Unix time counts seconds since January 1, 1970, at 00:00 UTC.
The conversion formula is:
Unix time = (Excel serial number - 25569) * 86400
- 25569 – Excel serial number for January 1, 1970 (the Unix epoch).
- 86400 – number of seconds in a day (24 × 60 × 60).
- Excel serial number – the value in the cell, e.g., 45000.5 for a date in 2023 at noon.
If your Excel uses the 1904 date system (common on older Macs), the epoch serial is 24107 instead of 25569. Check by looking at a cell with the value 1 – if it shows January 2, 1904, you need the 1904 formula.
Worked Example
Take the default Excel datetime from the calculator: 45000.5. Let’s convert it step by step.
- Identify the Excel serial number: 45000.5.
- Subtract the Unix epoch serial: 45000.5 – 25569 = 19431.5.
- Multiply by seconds per day: 19431.5 × 86400 = 1,678,881,600.
So the Unix time is 1,678,881,600 seconds. That corresponds to March 15, 2023, at 12:00:00 UTC. The calculator above gives the same result.
Why the Epoch Matters
Unix time was defined as the number of seconds elapsed since midnight UTC on January 1, 1970. That date was chosen for simplicity – it’s a round number that predates most modern operating systems. Excel, however, uses a different starting point: January 1, 1900 (or 1904 on some Mac versions). The 70-year gap between 1900 and 1970 is why you need to subtract a constant.
Understanding the epoch helps avoid confusion when you see large numbers like 1.6 billion – they’re not random; they’re just seconds counted from that fixed moment.
Time Zones and UTC
Unix time is always UTC. Excel, by default, stores local time without any time zone marker. This mismatch is the most common source of errors.
- If your Excel datetime is in local time (e.g., 12:00 PM EST), the formula above gives Unix time for that local moment, not the actual UTC moment.
- To convert correctly, you must first convert your local time to UTC. If you’re in a time zone that is UTC-5 (EST), subtract 5 hours from the Excel time before converting.
- In Excel, you can add or subtract hours using
TIME(hours,0,0). For example, to convert EST to UTC:=(A1 - TIME(5,0,0) - DATE(1970,1,1)) * 86400.
If your Excel data already includes time zone information (e.g., a column with UTC times), you can skip this step. Otherwise, decide whether you need the local Unix time or the true UTC Unix time.
Common Mistakes and Fixes
Several pitfalls trip up users. Here are the most frequent ones and how to avoid them.
- Using the wrong epoch for Mac (1904 system): If your Excel shows dates starting from 1904, replace 25569 with 24107. You can detect this by checking the serial number for 1 – if it displays January 2, 1904, you’re on the 1904 system.
- Forgetting to multiply by 86400: The serial number difference is in days, not seconds. Always multiply by 86,400.
- Rounding errors: Excel may show only a few decimal places. Use the full cell value or format the result as a number with 0 decimals.
- Negative Unix times: If your date is before 1970, the result will be negative. That’s valid – Unix time can be negative for dates before the epoch.
- Text dates: If your cell contains a text string like “2023-03-15 12:00”, convert it to a real date first using
DATEVALUEandTIMEVALUE.
Converting Unix Time Back to Excel
Sometimes you need to go the other way. To convert Unix seconds back to an Excel datetime, use the reverse formula:
Excel datetime = (Unix time / 86400) + 25569
Then format the cell as a date and time. For example, Unix time 1,678,881,600 divided by 86,400 equals 19,431.5, plus 25,569 gives 45,000.5 – the original Excel value.
This round-trip is useful when importing data from APIs or databases that use Unix timestamps.
Using Excel Functions and Formulas
You can also use Excel’s built-in functions to make the conversion more readable, especially if you need to handle time zones dynamically.
- Simple conversion:
=(A1 - DATE(1970,1,1)) * 86400– works on Windows and modern Mac (1900 system). - With time zone offset (e.g., UTC+2):
=((A1 - TIME(2,0,0)) - DATE(1970,1,1)) * 86400– subtract the offset if you’re ahead of UTC, add if behind. - Using TEXT to format: Not needed for the calculation, but you can display the result as a date with
=TEXT((B1/86400)+25569,"yyyy-mm-dd hh:mm:ss").
For large datasets, you can apply the formula to a whole column and then copy-paste values to replace the formulas if needed.
Frequently Asked Questions
Why is my result a decimal or scientific notation?
Excel may display large numbers in scientific notation (e.g., 1.68E+09). Change the cell format to ‘Number’ with 0 decimal places to see the full integer. The underlying value is correct.
Does this work for dates before 1970?
Yes, the formula works for any date. For dates before January 1, 1970, the result will be negative. That’s expected and correct – Unix time can be negative for earlier dates.
What about milliseconds? Many APIs use milliseconds.
Unix time is often expressed in milliseconds (e.g., 1678881600000). To get milliseconds, multiply the result by 1000: =(A1 - DATE(1970,1,1)) * 86400 * 1000.
How do I handle daylight saving time?
Daylight saving time (DST) changes your local offset from UTC. The simplest approach is to convert your local time to UTC using a reliable time zone database or Excel’s built-in functions that account for DST, such as CONVERT or custom VBA. Alternatively, if your data is already in UTC, you don’t need to worry about DST.
Why does my Mac give a different result?
Older Mac versions of Excel use the 1904 date system, where the serial number for January 1, 1970 is 24107 instead of 25569. Use the formula with 24107 if your dates start from 1904. Modern Excel for Mac uses the 1900 system by default, so the standard formula works.