To convert the timestamp into time, we need to use two functions for it, i.e. the TIME Function and the MID Function.
Here’s an example of how to convert the 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.
Now, the first thing that we will do is input the TIME Function. The formula for the same is:
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”.
For the “hour” in TIME Formula, we will use the MID Function, which is as follows:
The following requires text, start number and the number character.
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.
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:
As you can see there are in total 9 characters. So, the start number of the “hour” is 1.
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.
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.
The start number is 4. As we have discussed it earlier, the 23m starts from the 4rth place.
(00h23m04s – 123456789)
The number character will be 2 only. It is the same for hour, minute as well as second.
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.
The start number is 7.
(00h23m04s – 123456789)
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.
Overall, our formula becomes”
Now all we have to do is press “enter”.
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.
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:
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.
So, it’s easy, isn’t it?