Excel Convert Timestamp to Time (4 Easy Ways)

Download Practice Workbook

Converting Timestamp to Time.xlsm

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

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.

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.

This outputs the conversion of timestamps (range B5:B14) to time in range C5:C14.

Method 2 – Combine Excel TIME, MID, RIGHT & LEFT Functions to Convert Timestamp 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.

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.
This outputs the conversion of timestamps (range B5:B14) to time in range C5:C14.

Method 3 – Use Excel to Convert Unix Timestamp to Time

The sample dataset below has 10-digit timestamps (in seconds).

3.1. Excel DATE Function to Convert Unix Timestamp 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.

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.
This outputs the conversion of Unix timestamps (range B5:B14) to time in range C5:C14.

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

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.

Select range C5:C14 and change the Number format to Time.
This outputs the conversion of Unix timestamps (range B5:B14) to time in range C5:C14.

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

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.

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

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.

Click the Run button or press F5 to run the code.
This outputs the conversion of Unix timestamps (range B5:B14) to time in range C5:C14.

Method 4 – Use Excel Ribbon Options to Convert UTC Timestamp to Time

The sample dataset below has multiple UTC timestamps.

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

Select cell C5 and enter the formula into the cell.

=B5

Use Fill Handle to Autofill data from range C6:C14.

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

Note:

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

Go to Format Cells, select Time and under Type, select the time format.

The UTC timestamp will be converted into time in range C5:C14.

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

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.

The UTC timestamps (range B5:B14) will be converted to time in range C5:C14.

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

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.

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

The UTC timestamps (range B5:B14) will be converted to time in range C5:C14.

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

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.

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.
This outputs the conversion of UTC timestamps (range B5:B14) to time in range C5:C14.