When you export raw data from a server database, a web application, or a server log file, the dates and times are rarely formatted in a human-readable way. Instead, you will often find a ten-digit number, such as 1698753200. This is known as a Unix timestamp, which represents the total number of seconds that have elapsed since the 1st of January 1970 at midnight (UTC).
If you import this data into Microsoft Excel, it will simply treat these timestamps as large numbers. You cannot use standard Excel formatting to convert a Unix timestamp into a standard date. Instead, you must use a specific mathematical formula to translate the data into a format that Excel can understand.
Understanding How Excel Stores Dates
To convert a Unix timestamp, you first need to understand how Excel handles time. Unlike Unix, which counts seconds since 1970, Excel stores dates as serial numbers representing the total number of days since the 1st of January 1900.
Because the two systems use completely different starting points (epochs) and different units of measurement (seconds versus days), the conversion requires two mathematical steps: you must convert the Unix seconds into days, and then add those days to the correct starting date.
The Formula to Convert a Unix Timestamp
If your 10-digit Unix timestamp is located in cell A2, you can convert it using the following formula.
=(A2 / 86400) + DATE(1970,1,1)
How the Formula Works
- (A2 / 86400): There are exactly 86,400 seconds in a 24-hour day (60 seconds * 60 minutes * 24 hours). Dividing the Unix timestamp by this number converts the total seconds into the total number of days.
- + DATE(1970,1,1): This adds the total number of days to the Unix starting epoch (January 1st, 1970), effectively translating the Unix timeline into the Excel timeline.
Step-by-Step Instructions
- Click on the empty cell where you want the readable date to appear (for example, B2).
- Type the formula:
=(A2 / 86400) + DATE(1970,1,1) - Press Enter.
Initially, the cell will likely display a number with a decimal point (for example, 45230.45). Do not panic; the math is correct, but the cell is still formatted as a generic number.
Formatting the Cell as a Date
- Right-click the cell containing your new formula.
- Select Format Cells from the context menu.
- In the Number tab, select Date or Custom.
- Choose your preferred date and time format (for example,
dd/mm/yyyy hh:mm:ss) and click OK.
Your cell will now display the exact human-readable date and time.
How to Adjust for Local Time Zones
Unix timestamps are always recorded in Coordinated Universal Time (UTC). The formula above will convert the timestamp into a UTC date. If you need the final Excel date to reflect your local time zone, you must add or subtract the appropriate number of hours to the formula.
Because Excel measures time in days, you cannot simply add “+ 5” for 5 hours. You must add the fraction of a day that represents 5 hours (5 divided by 24).
For example, if you are located in a time zone that is UTC +5 hours, you would modify the formula like this:
=(A2 / 86400) + DATE(1970,1,1) + (5/24)
If you are in a time zone that is UTC -8 hours, you would subtract:
=(A2 / 86400) + DATE(1970,1,1) - (8/24)
By adjusting this final fraction, you can easily localise any server log directly within your spreadsheet.