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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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 Google PlusVisit Us On Youtube