# How to Convert 13 Digit Timestamp to Date Time in Excel (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

An unix timestamp indicates how much time has passed since January 1, 1970 (epoch time). When the timestamp has 11 digits, it displays seconds; when it has 13 digits, it displays milliseconds; and so on. This data is given in the Unix format. In this article, we will show you quick methods to convert the 13 digit timestamp to date time in Excel. We will be using Microsoft 365 to demonstrate the approaches. If you have Excel 2010 or higher, then you should be good to go.

## How to Convert 13 Digit Timestamp to Date Time in Excel: 3 Ways

There will be three handy approaches to convert the 13 digit timestamp to date and time in Excel. Firstly, we will combine the TIME, DATE, and an arithmetic formula to do so. Following that, we will use the CONVERT function. Lastly, we will insert a VBA code to achieve the goal of this article. Moreover, we have the following dataset. There are 13 digits for all the Unix timestamps. Additionally, we have shown the output in column D. ### 1. Combining Arithmetic Formula with DATE and TIME Functions

For the first method, we will combine the DATE, and TIME functions with an arithmetic formula to convert the 13 digit timestamp to date time in Excel. Additionally, we will need to use the custom format cells to return the output in the desired format.

Steps:

• Firstly, select the cell range C5:D10 and press Ctrl+1. This will bring up the Format Cells window.
• Secondly, select Date from the Category section.
• Thirdly, select the type similar to this: month/day/year hour:minute AM/PM. • After that, insert this formula in cell C5.

`=B5/86400000+DATE(1970,1,1)` • Then, press Enter, and using the Fill Handle, fill the rest of the cells. Formula Breakdown

• Firstly, 1 day = 24 hours = 24*60*60 seconds. Now, our 13-digit timestamp represents milliseconds. 1000 milliseconds = 1 second. Multiplying all these values, we have got the value 86400000.
• Then, we divide the timestamp by this value to get the number of days for that millisecond.
• Lastly, As we count the timestamp from the epoch time, we add the serial value of the date January 1, 1970, with the result.
• Moreover, if your timestamp is 16 digits, then it is given in microseconds. Therefore, you will need to divide it by 24*60*60*1000*1000 to convert it to days.

The output is in GMT time format. If you want to show it in a different time zone, then you will need to use the TIME function. For example, Easter Time is 4 hours behind the Greenwich Mean Time. So, we will need to subtract 4 hours from the GMT time. To do so, follow the steps:

• Now, type the following formula in cell D5 and press Enter.

`=C5-TIME(4,0,0)` • After filling the formulas to the rest of the cells, it will convert the 13 digit timestamp to date time (both in GMT and ET) in Excel. Similarly, you can convert it to other time zones. ### 2. Using CONVERT Function to Convert 13 Digit Timestamp to Date Time

We can use the CONVERT function to transform the 13 digit timestamp to day format. After that, we can add the serial number of the date 1 January 1970 (which is 25569) to return the timestamp values in date-time format in Excel.

Steps:

• Firstly, select the cell range C5:C10 and use the following formula.

`=CONVERT(B5,"msec","day")+25569` • Then, press Ctrl+Enter. This will AutoFill the formula to the selected range.
• Lastly, as shown in method 1, change the format to show the timestamp in date time format in Excel. ### 3. Applying VBA to Convert 13 Digit Timestamp to Date Time in Excel

We will insert an Excel VBA code to convert the 13-digit timestamp to date time in Excel. After inserting the code, you need to save the Excel file in xlsm or macro enable format. We will use the For Next loop to go through all the values of the dataset. Moreover, we have explained the code line by line, so it will be easier for anyone to understand.

Steps:

• To begin with, we have the following dataset. • Then, press Alt+F11 to bring up the Visual Basic window. Alternatively, you can do this by clicking Visual Basic from the Developer tab.
• After that, from the Insert tab, select Module.
• This will bring up the Module window, where we will type the code. • Then, type the following code in that window.
``````Option Explicit
Sub Convert_Timestamp13_To_Datetime()
Dim x2 As Integer
For x2 = 5 To 10
Cells(x2, 3).Value = Cells(x2, 2).Value / 86400000 + 25569
Next
Range("C5:C10").NumberFormat = "[\$-en-US]m/d/yy h:mm AM/PM;@"
End Sub`````` VBA Code Breakdown

``````Option Explicit
Sub Convert_Timestamp13_To_Datetime()
Dim x2 As Integer``````
• Firstly, we make it mandatory to declare all the variables using the Option Explicit statement.
• Secondly, we name our subroutine as Convert_Timestamp13_To_Datetime.
• Thirdly, we declare the variable type as an integer.
``````For x2 = 5 To 10
Cells(x2, 3).Value = Cells(x2, 2).Value / 86400000 + 25569
Next``````
• After that, we use a For Next loop to go through the cell range. Our output values should be in the cell range C5:C10.
• Cells(5,3) means row 5, column 3, so ultimately it means the first cell of the output range, which is C5.
• Then, We divide the value of cell B5 by 86400000 and add the Excel serial number for the date January 1, 1970.
• Afterward, the Next is used to go to the next value of the loop.
``````Range("C5:C10").NumberFormat = "[\$-en-US]m/d/yy h:mm AM/PM;@"
End Sub``````
• After that, we set the number format of the output cell range.
• Finally, we conclude the subroutine.
• Then, press Alt+F8 to bring up the Macro window.
• Afterward, select the Macro name and press Run. • By doing so, it will execute the code and show us the converted timestamp in date time format. ## Conclusion

We have shown you three easy methods to convert the 13-digit timestamp to date time in Excel. If you face any problems regarding these methods, feel free to comment below. Moreover, you can also leave any feedback for us, so we can serve you better.

Therefore, I hope this article will solve your problems and let us know if you face any problems in the comment box.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects. Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  