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

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.
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: 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")

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 our 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 Timestamp to Date in Excel (7 Easy Ways)


Similar Readings:


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


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.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo