Table of Contents
To convert a UNIX timestamp to a date in Excel, follow these steps:
1. In an empty cell, enter the UNIX timestamp that you want to convert.
2. Right-click on the cell and select “Format Cells” from the drop-down menu.
3. In the Format Cells window, go to the “Number” tab and select “Custom” from the Category section.
4. In the “Type” field, enter the following code: “yyyy-mm-dd hh:mm:ss” and click “OK.”
5. The cell will now display the date and time corresponding to the UNIX timestamp.
This method works because Excel stores dates and times as serial numbers, with January 1, 1900 being the first day represented as 1. A UNIX timestamp, on the other hand, is the number of seconds that have elapsed since January 1, 1970. By formatting the cell as a custom date and time, Excel is able to convert the UNIX timestamp into a recognizable date and time format.
Excel: Convert UNIX Timestamp to Date
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+25569You 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+25569We 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:
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I convert a UNIX timestamp to a date in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-convert-a-unix-timestamp-to-a-date-in-excel/
stats writer. "How can I convert a UNIX timestamp to a date in Excel?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-convert-a-unix-timestamp-to-a-date-in-excel/.
stats writer. "How can I convert a UNIX timestamp to a date in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-convert-a-unix-timestamp-to-a-date-in-excel/.
stats writer (2024) 'How can I convert a UNIX timestamp to a date in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-convert-a-unix-timestamp-to-a-date-in-excel/.
[1] stats writer, "How can I convert a UNIX timestamp to a date in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I convert a UNIX timestamp to a date in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.