Text to Time Conversion in Microsoft Excel

Original Question:-

How to convert text into time?

I have a text file that I want to import into Excel. I have a column that is
a time field. (Ex. 142200)(hh:mm:ss). When I import it and format it in time,I always get 00:00:00. Is there a command that I need to convert it?

User query: -When we export the text file in Excel file then time format gets changed. Even after applying the time formatting, it is not converting into right time format.


image 1


Our one of the users has replied: - These numbers will be interpreted as hh:mm:ss
with either opening or importing a text file. 142200, since there's no fractional part (right of the decimal point -- the part that indicates a fraction of a day, or time) will always be zero. This value is a date of 29Apr 2289, at midnight.

=TIME(LEFT(A2,2),MID(A2,3,2), RIGHT(A2,2))
Then this column would be formatted as hh:mm:ss, or according to your desired format.

How to resolve this problem?

Follow below given steps to covert the text in to time:-

  • Enter the formula in cell B2.
  • =--TEXT(A2,"00\:00\:00")
  • Function will return the number.
  • Format the cells in to hh:mm:ss
  • Copy the same formula in the range.

image 2


This is the way we can convert the text into time by using the Text function.



Users are saying about us...

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube