A UNIX timestamp represents the number of seconds that have passed since January 1, 1970.
You can use the following formulas in Excel to convert a UNIX timestamp to a recognizable date:
Method 1: Convert Timestamp to Date (e.g. 3/29/2022)
=INT(((A2/60)/60)/24)+DATE(1970,1,1)
Method 2: Convert Timestamp to Date with Time (e.g. 03/29/2022 14:50:00)
=A2/86400+25569
You then must use the Number Format option to specify mm/dd/yyyy hh:mm:ss as the format.
Both formulas assume that the UNIX timestamp is located in cell A2.
The following example shows how to use each formula in practice with the following column of timestamps in Excel:
Example 1: Convert UNIX Timestamp to Date in Excel
Suppose we would like to convert each timestamp in column A into a date in column B.
To do so, we can type the following formula into cell B2:
=INT(((A2/60)/60)/24)+DATE(1970,1,1)
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays each UNIX timestamp from column A as a recognizable date.
Example 2: Convert UNIX Timestamp to Date with Time in Excel
Suppose we would like to convert each timestamp in column A into a date with a time in column B.
To do so, we can type the following formula into cell B2:
=A2/86400+25569
We can then click and drag this formula down to each remaining cell in column B:
Next, highlight the cell range B2:B10, then click the tiny arrow in the bottom right corner of the Number group on the Home tab, then choose Custom as the Category, then type mm/dd/yyyy hh:mm:ss as the Type:
Once you click OK, column B now displays each UNIX timestamp from column A as a date with the hours, minutes and seconds:
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Calculate Difference Between Two Times in Hours
Excel: Calculate Difference Between Two Times in Minutes
Excel: How to Calculate Average Time
