Excel Convert Timestamp to Time (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

The timestamp is widely used to record and analyze time-based information, such as financial transactions, online meetings, and event logs. However, dealing with raw timestamp data can be challenging without proper formatting. That’s where Excel comes in handy. Excel enables you to convert timestamps into easily readable time formats. In short, we can use Excel to convert timestamp to time.

Overview of Excel Convert Timestamp to Time


Download Practice Workbook

You can download this practice book while going through the article.


Excel to Convert Timestamp to Time: 4 Easy Methods

We can use Excel to convert timestamp to time. We have discussed 4 methods in this article. Different Excel functions and features are used to convert timestamp to time. We have converted Unix, UTC, and other timestamp formats into time in Excel. We have used the below image initially to convert timestamp to time.

Dataset for Timestamp


1. Convert Timestamp to Time Using Combination of TIME and MID Functions

We can use the combination of Excel TIME and MID functions to convert timestamp to time. In the dataset, we have text timestamps of 9 digits. The first two digits represent hours, the 4th and 5th digits represent minutes, and the 7th and 8th digits represent seconds. Follow the steps below to convert the timestamps to time.

  • Select cell C5 and enter the formula into the cell.
=TIME(MID(B5,1,2),MID(B5,4,2),MID(B5,7,2))
  • Use Fill Handle to Autofill data in range C6:C14.

Combining TIME and MID Functions

Formula Breakdown

  • MID(B5,1,2),MID(B5,4,2),MID(B5,7,2)

The MID function extracts portions of the text string in cell B5 based on character position from the 2nd argument and number of characters based on the 3rd argument. MID(B5,1,2) extracts the hour component (00), MID(B5,4,2) extracts the minute component (08), and MID(B5,7,2) extracts the second component (05) from the timestamp.

  • TIME(MID(B5,1,2),MID(B5,4,2),MID(B5,7,2))

The outputs from the MID functions are directly fed to the Time function as arguments.

Result: 12:08 AM

  • Select range C5:C14 and change the Number format to Time.

Selecting Time Format from the Number Group

  • Now, you will see the conversion of timestamps (range B5:B14) to time in range C5:C14.

Converting Timestamp to Time using combination of TIME and MID functions


2. Combine Excel TIME, MID, RIGHT & LEFT Functions to Convert Timestamp to Time

We can combine Excel TIME, MID, RIGHT, and LEFT functions to convert timestamp to time. Simply follow the steps below to convert the timestamps to time.

  • Select cell C5 and enter the formula into the cell.
=TIME(LEFT(B5,2),MID(B5,4,2),RIGHT(B5,2))
  • Use Fill Handle to Autofill data from range C6:C14.

Combining TIME, MID, RIGHT, and LEFT Functions

Formula Breakdown

  • LEFT(B5,2)

The LEFT function extracts two components from the left or start of cell B5.

Result: 00

  • MID(B5,4,2)

The MID function extracts two-minute components from the fourth position.

Result: 08

  • RIGHT(B5,2)

The Right function extracts two components from the right end of cell B5.

Result: 05

  • TIME(LEFT(B5,2),MID(B5,4,2),RIGHT(B5,2))

The outputs of LEFT, MID and RIGHT functions are directly fed to the Time function as arguments.

Result: 12:08 AM

  • Select range C5:C14 and change the Number format to Time.
  • Now you will see the conversion of timestamps (range B5:B14) to time in range C5:C14.

Combining TIME, MID, RIGHT, and LEFT Functions to Convert Timestamp to Time


3. Use Excel to Convert Unix Timestamp to Time

The Unix timestamp indicates how much time has passed since January 1, 1970 (epoch time). The timestamp can be expressed in both seconds and milliseconds. The timestamp will have 10 digits when it is in seconds. On the other hand, the timestamp will have 13 digits when it is in milliseconds. Here in the dataset, we have 10-digit timestamps (in seconds). We will use Excel to convert the Unix timestamp to the time in the GMT time zone.

Dataset for Unix Timestamp


3.1. Excel DATE Function to Convert Unix Timestamp to Time

We can use the DATE function to convert Unix timestamp to time. Just follow the steps below to convert the timestamps to time.

  • Select cell C5 and enter the formula into the cell.
=(B5/86400)+DATE(1970,1,1)
  • Use Fill Handle to Autofill data from range C6:C14.

Applying Excel DATE Function

Formula Breakdown

  • (B5/86400)

This formula divides the value of cell B5 by 86400 (the number of seconds in a day).

Result: 12831.42019

  • DATE(1970,1,1)

This portion of the formula gets a number that represents the date January 1, 1970.

Result: 01-01-1970

  • (B5/86400)+DATE(1970,1,1)

This formula calculates the number of days since January 1, 1970.

Result: 38400.42019

  • Select range C5:C14 and change the Number format to Time.
  • Now you will see the conversion of Unix timestamps (range B5:B14) to time in range C5:C14.

Applying Excel DATE Function to Convert Unix Timestamp to Time


3.2. Apply the CONVERT Function in Excel to Convert Unix Timestamp to Time

We can use the CONVERT function to convert Unix timestamp to time. You need to follow the steps below.

  • Select cell C5 and enter the formula into the cell.
=CONVERT(B5,"sec","day")+25569
  • Use Fill Handle to Autofill data from range C6:C14.

Using Excel CONVERT Function

  • Select range C5:C14 and change the Number format to Time.
  • Now you will see the conversion of Unix timestamps (range B5:B14) to time in range C5:C14.

Using Excel CONVERT Function to Convert Unix Timestamp to Time


3.3. Apply VBA in Excel to Convert Unix Timestamp to Time

We can apply Excel VBA to convert Unix timestamp to time. Simply follow the steps below.

  • First, you have to launch the VBA macro editor from your workbook. You may Follow this article: How to Write VBA Code in Excel or go to the Developer tab >> Visual Basic.

Opening Visual Basic from the Developer Tab

  • Go to Insert >> Module.
  • Paste the following code in your VBA Macro Editor.

Inserting Code into VBA Editor Module

Sub Timestamp_to_Time()
    Dim myRng As Range
    Dim WS As Worksheet
    Set WS = ActiveSheet
    Set myRng = WS.Range("B5:C14")
    For i = 1 To myRng.Rows.Count
        myRng.Cells(i, 2) = (myRng.Cells(i, 1) / 86400) + 25569
        myRng.Cells(i, 2).NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Next i
End Sub

VBA Breakdown

Sub Timestamp_to_Time()
  • This line defines a new subroutine called Timestamp_to_Time.
    Dim myRng As Range
    Dim WS As Worksheet
  • Two variables are declared. myRng is a variable of type Range and WS is a Worksheet type variable.
    Set WS = ActiveSheet
  • This line assigns the active worksheet to the variable WS.
    Set myRng = WS.Range("B5:C14")
  • This line assigns the range B5:C14 to the variable myRng.
    For i = 1 To myRng.Rows.Count
        myRng.Cells(i, 2) = (myRng.Cells(i, 1) / 86400) + 25569
        myRng.Cells(i, 2).NumberFormat = "[$-x-systime]h:mm:ss AM/PM"   
    Next i
  • A For loop is initiated. Within the loop, the value in the cell at the i-th row and the 1st column of myRng is divided by 86400 (the number of seconds in a day). This division converts the timestamp value to a decimal representation of the date and time. The decimal representation is then added to 25569, which corresponds to the date serial number for January 1, 1970, in Excel’s date system. The resulting value is assigned back to the cell at the i-th row and the 2nd column of myRng, effectively converting the timestamp to a date and time value. The NumberFormat property of the cell is set to “[$-x-systime]h:mm:ss AM/PM” to format the time as “hour:minute:second AM/PM“.
End Sub
  • This line ends the subroutine.

  • Press the Run button or F5 key to run the code.
  • After running the code, you will see the conversion of Unix timestamps (range B5:B14) to time in range C5:C14.

Applying Excel VBA to Convert Unix Timestamp to Time


4. Use Excel Ribbon Options to Convert UTC Timestamp to Time

We can use Excel to convert UTC timestamp to time. Here in the dataset, we have multiple UTC timestamps. We will use different Excel functions and formulas to convert them into time.

Dataset for UTC Timestamp


4.1. Use Format Cells Option in Excel to Convert UTC Timestamp to Time

We can use the Format Cells option in Excel to convert UTC timestamp to time. Follow these steps.

  • Select cell C5 and enter the formula into the cell.
=B5
  • Use Fill Handle to Autofill data from range C6:C14.

Using Fill Handle to Autofill Data

  • Select range C5:C14 and right-click.
  • Click the Format Cells option from the Context Menu.

Selecting Format Cells Option from the Context Menu

Note:

You can also use keyboard shortcut Ctrl + 1 to avail Format Cells window.

  • Choose the appropriate time format from the Number tab of Format Cells window and press OK.

Selecting Appropriate Time Format

  • Now you will see the UTC timestamp converted into time in range C5:C14.

Applying Format Cells Option to Convert UTC Timestamp to Time


4.2. Apply Excel TEXT Function to Convert UTC Timestamp to Time

We can use the TEXT function to convert UTC timestamp to time. Just follow the steps below.

  • Select cell C5 and enter the formula into the cell.
=TEXT(B5,"h:mm:ss AM/PM")
  • Use Fill Handle to Autofill data from range C6:C14.

Using Excel TEXT Function to Convert UTC Timestamp to Time

Now you will see the conversion of UTC timestamps (range B5:B14) to time in range C5:C14.


4.3. Combine Excel TEXT and TRUNC Functions to Convert UTC Timestamp to Time

We can combine Excel TEXT and TRUNC functions to convert UTC timestamp to time. Follow the steps below.

  • Select cell C5 and enter the formula into the cell.
=TEXT(B5-TRUNC(B5), "hh:mm:ss AM/PM")
  • Use Fill Handle to Autofill data from range C6:C14.

Combining Excel TEXT and TRUNC Functions to Convert UTC Timestamp to Time

Formula Breakdown

  • TRUNC(B5)

This portion of the formula removes the decimal part of the value in cell B5, leaving only the date portion.

Result: 2-17-05 12:00 AM

  • B5-TRUNC(B5)

This subtracts the date portion from the original value in cell B5, leaving only the time portion.

Result: 0.420185185

  • TEXT(B5-TRUNC(B5), “hh:mm:ss AM/PM”)

This formula formats the time portion. The “hh” represents hours in 12-hour format, “mm” represents the minutes, and the “ss” represents the seconds. “AM” or “PM” represents the time period.

Result: 10:05:04 AM

You can see the conversion of UTC timestamps (range B5:B14) to time in range C5:C14.


4.4. Apply Excel TIME with HOUR, MINUTE & SECOND Functions to Convert UTC Timestamp to Time

We can apply the Excel TIME function with HOUR, MINUTE, and SECOND functions to convert UTC timestamps to time. Simply follow the steps below.

  • Select cell C5 and enter the formula into the cell.
=TIME(HOUR(B5), MINUTE(B5), SECOND(B5))
  • Use Fill Handle to Autofill data from range C6:C14.

Applying TIME with HOUR, MINUTE and SECOND Functions

Formula Breakdown

  • HOUR(B5)

The HOUR function is used to extract the hour component from the time value in cell B5.

Result: 10

  • MINUTE(B5)

The MINUTE function is used to extract the minute component from the time value in cell B5.

Result: 05

  • SECOND(B5)

The SECOND function is used to extract the second component from the time value in cell B5.

Result: 04

  • TIME(HOUR(B5), MINUTE(B5), SECOND(B5))

The TIME function takes the extracted hour, minute, and second values as arguments and creates a new time value.

Result: 10:05 AM

  • Select range C5:C14 and change the Number format to Time.
  • Now you will see the conversion of UTC timestamps (range B5:B14) to time in range C5:C14.

Applying TIME with HOUR, MINUTE and SECOND Functions to Convert UTC Timestamp to Time


4.5. Apply Text to Columns Wizard in Excel to Convert UTC Timestamp to Time

We can apply Text to Columns wizard in Excel to convert UTC timestamp to time. For this method, we have used a 24 hour format UTC timestamp.

Dataset for Applying Text to Columns Wizard

Follow the steps below.

  • First, select range B5:B14 and go to the Data tab.
  • Click on Text to Columns options.

Selecting Text to Columns Option

  • In Step 1 of 3, choose the Delimited option and press Next.

Step 1 of Text to Columns Wizard

  • In Step 2 of 3, select Space and Treat consecutive delimiters as one option.
  • Then, press Next.

Step 2 of Text to Column Wizard

  • In Step 3 of 3, for the first column, select the Do not import column (skip) and set the Destination to cell C5. Then, press Finish.

Step 3 of Text to Columns Wizard

  • Now, you will see the output time in the range C5:C14.

Applying Text to Columns Wizard to Convert UTC Timestamp to Time

Note:

You should change the time in your pc into a 24-hour format to get accurate results.


Convert 13 Digit Timestamp to Date and Time in Excel

The Unix timestamp can be of 13-digits when it is expressed in milliseconds. Here in the dataset, we have 13-digit Unix timestamps. We will use Excel DATE function to convert these 13-digit timestamps to date and time.

Dataset for 13 Digit Timestamp

Follow the steps below.

  • Select cell C5 and enter the formula into the cell.
=(B5/86400000)+DATE(1970,1,1)
  • Use Fill Handle to Autofill data from range C6:C14.

Using Fill Handle to Autofill Data with Excel DATE Formula

Formula Breakdown

  • (B5/86400000)

This divides the value of cell B5 by 86400000 (number of milliseconds in a day).

Result: 12831.42019

  • DATE(1970,1,1)

This portion of the formula gets a number that represents the date January 1, 1970.

Result: 01-01-1970

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

This formula calculates the number of days since January 1, 1970.

Result: 38400.42019

  • Select range C5:C14 and right-click.
  • Click the Format Cells option from the Context Menu.

Selecting Format Cells Option

  • Choose the appropriate date and time format and press OK.

Choosing Appropriate Date Formation

  • Now you will see the conversion of 13-digit timestamps (range B5:B14) to date and time in range C5:C14.

Converting 13 Digit Timestamp to Date and Time


Things to Remember

There are a few things to remember while using Excel to convert timestamp to time:

  • Convert the time into Time formation from the Number group in Home tab in Excel.
  • Use proper functions when converting timestamps into time.
  • You need to be careful about the time zone and time format in your pc.

Frequently Asked Questions

  • How do I handle time zones while converting timestamps into time in Excel?

To handle time zones, you need to adjust the timestamp by applying a time zone offset or using a conversion formula by adding or subtracting the time zone difference.

  • Why is my converted time showing as a decimal value?

If your converted time appears to be a decimal value, it may be due to incorrect formatting. Apply the Time formatting from the Number group in your Excel.

  • How do I calculate the time difference between two timestamps?

To calculate the time difference between two timestamps, you can subtract the earlier timestamp from the later timestamp. The result will be a decimal value representing the time difference in days, which can be formatted as desired.


Conclusion

In this article, we have discussed 4 easy methods in detail to use Excel to convert timestamp to time. This article will allow users to use Excel more efficiently and effectively.  If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, Exceldemy.com, and unlock a great resource for Excel-related content.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abu Sina Ibne Albaruni
Md. Abu Sina Ibne Albaruni

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo