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.
Read More: How to Convert Week Number to Date in Excel
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.
Read More: How to Convert Active Directory Timestamp to Date 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.
Download Practice Workbook
You can download the Excel file from the link below.
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.
Related Articles
- How to Convert Timestamp to Date in Excel
- How to Convert Unix Timestamp to Date in Excel
- How to Convert SAP Timestamp to Date in Excel
<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
