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

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 UTC. So a Unix timestamp is the number of elapsed seconds from the Unix Epoch to that particular date.

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 and want to convert it to a date, we have to divide it by 86400 (the number of seconds in one day, 24*60*60) to get the number of days passed from the Unix Epoch, then add the date value (serial number for 1st January 1900) of the Unix Epoch date.

We calculate this with the following formula:

=(unix timestamp value/86400)+ DATE(1970,1,1)

The DATE function returns the date value, namely the sequential serial number of a particular date. The syntax of the DATE function is =DATE(year, month, day), so we enter 1970,1,1 as the arguments to calculate the date value of the Unix Epoch.

Then we need to apply one of the following methods to convert the returned summed serial no to an Excel date.

Method 1 – Format Cells

We can apply different formatting by using the Format Cells options in Excel.

Step 1 – Convert the Unix Timestamps into Serial Numbers

We have a list of Unix timestamps in cells B5:B9 to convert to dates.

We’ll turn them into serial numbers, and then apply the date format to convert them into Excel dates.

In cell C5, enter the following formula and press Enter.

=(B5/86400)+DATE(1970,1,1)

Step 2 – Different Ways to Open the Format Cells Options

2.1 – Keyboard Shortcuts

Select cell C5.
Press Ctrl +1 or Alt + H + FM.

2.2 – Using the Context Menu

Select cell C5.
Right-click the mouse and choose the Format Cells option.

2.3 – Using the Format Tab

Steps:

Select cell C5.
Go to the Home tab on the Excel Ribbon.
Click the Format option.
Select the Format Cells option.

Step 3 – Apply Date Format to Convert Serial Number to Excel Date

In the Format Cells window:

From the Number tab, click the Date category.
Choose your preferred date format from the list. In this example, we chose the first one.

Use the Fill Handle at the bottom right corner of cell C5 to drag the formula down to cells C6:C9.

The converted Excel dates are returned.

Method 2 – Using the TEXT Function

STEPS:

In cell C5, enter 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”, the desired date format that we want to display.

Using the Fill Handle, copy the formula to the other cells.

Method 3 – Apply Number Format

Excel provides easy options to change the Number Format of a cell value.

Steps:

In cell C5, enter 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.

Use the Fill Handle to copy this number format to cells 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.