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).

Time Zone Adjustment (Optional):

- 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:

Prepare Your Dataset:
- 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.