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

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.


Download Practice Workbook

You can download the Excel file from the link below.


3 Handy Approaches to Convert 13 Digit Timestamp to Date Time in Excel

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.

3 Handy Approaches to Convert 13 Digit Timestamp to Date Time in Excel


1. Combining Arithmetic Formula with DATE and TIME Functions

For the first method, we will combine the DATE, 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.

Combining Arithmetic Formula with DATE and Time Functions

  • After that, insert this formula in cell C5.

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

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 Unix Timestamp to Date in Excel (3 Methods)


Similar Readings


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

Using CONVERT Function for Converting 13 Digit Timestamp to Date Time Excel

Read More: How to Convert Active Directory Timestamp to Date in Excel (4 Methods)


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.

Applying VBA to Convert 13 Digit Timestamp to Date Time in Excel

  • 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 to Convert 13 Digit Timestamp to Date Time in Excel

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.

Read More: How to Convert Timestamp to Date in Excel (7 Easy Ways)


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.

However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Moreover, you can visit our site, ExcelDemy, for more Excel-related articles. Thanks for reading. Keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo