Al Arafat Siddique
This article explains 3 methods to convert unix timestamp to date in Excel. The Unix timestamp format is widely used in different operating and file systems. It holds time as the number of seconds elapsed from January 1, 1970, 00:00.
Introduction to the Unix Timestamp
The Unix timestamp is a system of tracking time as a running total of seconds. The time count started at the Unix Epoch on January 1st, 1970 at UTC. So, a Unix timestamp is nothing but the number of elapsed seconds from the Unix Epoch to that particular date.
How to Convert Unix Timestamp to Date in Excel: 3 Methods
Microsoft Excel stores a date as a sequential serial number which starts from 1st January 1900 and has an increment of 1 for each day onwards. So, when we are given a Unix timestamp, we have to divide it by 86400 (no of seconds of one day, 24*60*60). By doing this, we’ll get the number of days passed from the Unix Epoch which is similar to a serial number. After that, we have to add the date value (serial number from 1st January 1900) for the Unix Epoch on January 1st, 1970. To get this, we need to use the following formula.
=(unix timestamp value/86400)+ DATE(1970,1,1)The DATE function returns the date value i.e., the sequential serial number of a particular date. The syntax of the DATE function is =DATE(year, month, day). We need to put 1970,1,1 as arguments as we want to calculate the date value of the Unix Epoch.
Finally, we need to follow one of the following methods to convert the summed serial no to Excel date.
1. Format Cells to Convert Unix Timestamp to Date
We can apply different formatting by using the Format Cells options in Excel to convert the Unix timestamps into the date format. Let’s follow the steps below to accomplish this.
Step 1: Convert the Unix Timestamps into Serial Numbers
We have a list of Unix timestamps in cells B5:B9 to convert them to date.
At first, we’ll turn them into serial numbers and then apply the date format to convert them into Excel dates. In cell C5, put the following formula and press Enter.
=(B5/86400)+DATE(1970,1,1) 
Step 2: Different Ways to Open the Format Cells Options
1. Keyboard Shortcuts to Open Format Cells Options
- Select cell C5.
- Press Ctrl +1 or Alt + H + FM to open the Format Cells window.
2. Context Menu to Open Format Cells Options
- Select cell C5.
- Right-click the mouse and choose the Format Cells option.
3. Open Format Cells Options Using Format Tab
Steps:
- Select cell C5.
- Go to the Home tab from Excel Ribbon.
- Click the Format option.
- Choose the Format Cells option.
Step 3: Apply Date Format to Convert Serial Number to Excel Date
Now that we have the Format Cells window opened,
- From the Number tab, click the Date category.
- Then choose your preferred date format from the list. In this example, we chose the first one.
- Now locate the Fill Handle at the bottom right corner of cell C5 and drag it down to cells C6:C9.
- Here are the converted Excel dates.
Similar Readings
- How to Convert Timestamp to Date in Excel
- How to Convert 13 Digit Timestamp to Date Time in Excel
- How to Convert Active Directory Timestamp to Date in Excel
2. Use of the TEXT Function to Convert Unix Timestamp to Date in Excel
We can also use the TEXT function to convert Unix timestamp values to Excel dates. In cell C5, put the following formula and press Enter.
=TEXT((B5/86400)+DATE(1970,1,1),"m/d/yyyy")
Formula Breakdown:
The TEXT function has 2 arguments: value and format_text.
value: B5/86400)+DATE(1970,1,1) which converts the Unix timestamp value to serial no.
format_text : “m/d/yyyy”, we can put the desired date format we want to display.
Now using the Fill Handle, we can copy and paste the formula to the other cells.
Read More: How to Convert Serial Number to Date in Excel
3. Apply Number Format to Convert Unix Timestamp to Date in Excel
Excels provides easy options to change the Number Format of a cell value. In this example, we’re going to use this functionality to achieve our goal. Let’s follow the following steps.
Steps
- In cell C5, put the following formula.
=(B5/86400)+DATE(1970,1,1) 
- Select cell C5.
- Go to the Home Tab.
- Click the dropdown for Number Format.
- Now choose either Short Date or Long Date. We chose the Short Date option here.
- Now using the FIll Handle we can copy this number format to cell C6:C9.
Similar Readings:
- How to Convert Number to Date in Excel
- How to Convert 8 Digit Number to Date in Excel
- How to Convert Number (YYYYMMDD) to Date Format in Excel
Things to Remember
- If we analyze the outputs, we see that for the Unix timestamp value difference of 86400, there is a one-day advancement from 1/1/1970 to 1/2/1970 that we described earlier.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to convert a Unix timestamp to an Excel date using 3 different methods. Hopefully, it will help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Related Articles
- How to Convert General Format to Date in Excel
- How to Convert Week Number to Date in Excel
- How to Convert SAP Timestamp to Date in Excel
- How to Disable Auto Convert to Date in Excel
- Excel VBA to Convert Date and Time to Date Only
- How to Convert Text to Date in Excel
- How to Convert Text to Date and Time in Excel
- How to Convert Text Date and Time to Date Format in Excel
- Text Won’t Convert to Date in Excel
