# 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. 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. ### 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. ### 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. ## 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. ## 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 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 Advanced Excel Exercises with Solutions PDF  