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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
3 Methods to Convert Unix Timestamp to Date in Excel
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 that 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 an 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.
Read More: Convert Serial Number to Date in Excel (7 Easy Ways)
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 our 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 Timestamp to Date in Excel (7 Easy Ways)
Similar Readings:
- How to Convert Active Directory Timestamp to Date in Excel (4 Methods)
- Convert Text Date and Time to Date Format in Excel (7 Easy Ways)
- Text Won’t Convert to Date in Excel (4 Problems & Solutions)
- How to Convert Text to Date in Excel (10 ways)
- How to Insert Static Date in Excel (4 Simple Methods)
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.
Notes
- If we analyze the outputs, we see that for Unix timestamp value difference of 86400, there is one day advancement from 1/1/1970 to 1/2/1970 that we described earlier.
Read More: How to Convert Number to Date in Excel (6 Easy Ways)
Conclusion
Now, we know how to convert a Unix timestamp to an Excel date using 3 different methods. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
