How to Convert Unix Timestamp to Date in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.
Convert Unix Timestamp to Date in Excel

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) 

Convert Unix Timestamp to Date in Excel


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.

Convert Unix Timestamp to Date in Excel

  • Now locate the Fill Handle at the bottom right corner of cell C5 and drag it down to cells C6:C9.

Convert Unix Timestamp to Date in Excel

  • Here are the converted Excel dates.

Convert Unix Timestamp to Date in Excel

Read More: How to Convert 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")

Convert Unix Timestamp to Date in Excel

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.

Convert Unix Timestamp to Date in Excel

Read More: How to Convert 13 Digit Timestamp to Date Time 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) 

Convert Unix Timestamp to Date in Excel

  • 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.

Convert Unix Timestamp to Date in Excel

  • Now using the FIll Handle we can copy this number format to cell C6:C9.

Convert Unix Timestamp to Date 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


<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo