To convert the timestamp into time, we need to use two functions for it, i.e. the TIME Function and the MID Function.

Previously: How to get the number of days between dates in Excel?

Here’s an example of how to convert the timestamp into time:

Convert timestamp into time

As you can see in the image above, 6 timestamps are given and we have separated the hour, minute and second from the same.

Just to be clear, this is not a requirement, this is just for understanding. Our work is with the timestamp only.

Convert timestamp into time

Now, the first thing that we will do is input the TIME Function. The formula for the same is:

=TIME(

This formula requires hour, minute, second.

Now as we have discussed above that the whole conversion involves the work of timestamp only, so we will not use the values written in “Hour”, “Minute” and “Second”.

Convert timestamp into time

For the “hour” in TIME Formula, we will use the MID Function, which is as follows:

=MID(

The following requires text, start number and the number character.

Convert timestamp into time

The “text” in the MID Function is our Timestamp. So, we will select the same, in the above example the timestamp is 00h23m04s or in other words cell B3.

Convert timestamp into time

Then we will hop on to the start number. When we talk about start number, it means the starting place overall.

To understand the same, you need to get this:

00h23m04s

123456789

As you can see there are in total 9 characters. So, the start number of the “hour” is 1.

Convert timestamp into time

Then we will come to the number characters, as we have learnt above regarding the total number of characters, so we can tell that the total number of characters that hour has is 2.

Always remember we do not count the h, m, and s in it. Though if we come to the minute aspect, the start number will be 4, still we ignore h, m and s in the “number character” part.

If you still have doubt regarding the same, here’s a simple way to understand it:

It says “number” characters, which means you will only keep the count of those characters which upholds number and not alphabet.

Convert timestamp into time

After jotting down the number characters, we will close the MID Function for hour and we will again use the MID Function for minute.

As we know that the text is the timestamp only, so we will select cell B3.

Convert timestamp into time

The start number is 4. As we have discussed it earlier, the 23m starts from the 4rth place.

(00h23m04s – 123456789)

Convert timestamp into time

The number character will be 2 only. It is the same for hour, minute as well as second.

Convert timestamp into time

Then we will close the MID Function of minute and start with the second.

Again, we will input the MID Function and select the text as the timestamp, i.e. cell B3.

Convert timestamp into time

The start number is 7.

(00h23m04s – 123456789)

Convert timestamp into time

Then we will input the number characters i.e. 2.

And we will close the function. After doing the same, we will close the time function as well.

Convert timestamp into time

Overall, our formula becomes”

=TIME(MID(B3,1,2),MID(B3,4,2),MID(B3,7,2))

Now all we have to do is press “enter”.

Convert timestamp into time

Here comes the Time: 12:23:04 AM

Now, all we have to is simply drag down this formula, so that our work gets easily done for the rest of the timestamps.

Convert timestamp into time

Here you go.

There is one thing that needs to be cleared. At times, your time format does not show the second. So, all you can do is:

Convert timestamp into time

You need to go to the “Number” section in excel, open the drop-down menu where all the number formats are given and click on “more number formats”.

There you can choose the type of Time format you need. If you want to add second you can, if you want to remove it you can. You can even change it from 12-hour format to 24-hour format or vice versa.

Convert timestamp into time

So, it’s easy, isn’t it?