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

## Dataset Overview

A Unix timestamp indicates the amount of time that has passed since January 1, 1970 (epoch time). When the timestamp has 11 digits, it represents seconds; when it has 13 digits, it represents milliseconds, and so on. In this article, we’ll explore quick methods to convert a 13-digit timestamp to date and time in Excel. We’ll be using Microsoft 365 to demonstrate these approaches. Excel 2010 or a higher version, supports these approaches.

We’ll use the following dataset. There are 13 digits for all the Unix timestamps. We have shown the output in column D.

### Method 1 – Combining Arithmetic Formula with DATE and TIME Functions

• Select the Cell Range:
• Highlight the cell range C5:D10 and press Ctrl+1 to open the Format Cells window.
• Choose Date Format:
• In the Format Cells window, select the Date category.
• Choose a format similar to this: month/day/year hour:minute AM/PM.

• Insert the Formula:
• Insert the following formula in cell C5:

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

• Press Enter.
• Use the Fill Handle to apply the formula to the remaining cells.

Formula Breakdown

• 1 day = 24 hours = 24 * 60 * 60 seconds.
• Our 13-digit timestamp represents milliseconds (1000 milliseconds = 1 second).
• Multiply these values to get 86400000.
• Divide the timestamp by this value to get the number of days.
• Add the serial value of January 1, 1970, to the result (since we count from epoch time).

• If you want to display the output in a different time zone, use the TIME function.
• For example, to convert to Eastern Time (ET), subtract 4 hours from the GMT time.
• 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.
• Now, enter 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.

### Method 2 – Using CONVERT Function

• Select the Cell Range:
• Highlight the cell range C5:C10.
• Apply the Formula:
• Enter the following formula:

`=CONVERT(B5,"msec","day")+25569`

• Press Ctrl+Enter to Autofill the formula.
• Format the Timestamp:
• As shown in Method 1, adjust the format to display the timestamp in date-time format in Excel.

### Method 3 – Using VBA to Convert 13-Digit Timestamp to Date and Time in Excel

In this method, we’ll insert an Excel VBA code to convert a 13-digit timestamp to date and time in Excel. Follow these steps:

• Begin with the dataset containing the 13-digit timestamps.

• Access the Visual Basic Window:
• Press Alt+F11 to open the Visual Basic for Applications (VBA) window.
• Alternatively, you can click Visual Basic from the Developer tab.
• Insert a Module:
• From the Insert tab, select Module.
• This opens the Module window where we’ll enter our code.

• Enter the VBA Code:
``````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: Ensures all variables are explicitly declared.
• Convert_Timestamp13_To_Datetime: Our subroutine name.
• Dim x2 As Integer: Declares an integer variable.
• The For Next loop processes the cell range (C5:C10).
• We divide the value in cell B5 by 86400000 (milliseconds in a day) and add the Excel serial number for January 1, 1970.
• The NumberFormat line sets the desired date-time format.
• Execute the Macro:
• Press Alt+F8 to open the Macro window.
• Select the macro name (e.g., Convert_Timestamp13_To_Datetime) and click Run.

• The code will convert the timestamps to date and time format.

## Related Articles

<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF