Converting Text to Time in Microsoft Excel . I‘ve encountered an issue while importing a text file into Excel where the time format isn’t converting correctly, despite applying the appropriate formatting. One user suggested that the numbers, such as ‘142200’ without a fractional part, are interpreted as dates, specifically April 29, 2289, at midnight. They proposed a solution using a formula:
Converting Text to Time in Microsoft Excel
=TIME(LEFT(A2,2),MID(A2,3,2), RIGHT(A2,2))
After applying this formula in a new column and formatting it as desired (‘hh:mm:ss’), the issue was resolved.
To replicate this fix:
Input the formula into cell B2: =–TEXT(A2,”00\:00\:00″).
This formula will return the numeric value.
Format the cells to display ‘hh:mm:ss’.
Copy the formula across the range of cells.
Following these steps resolves the text-to-time conversion issue by utilizing the TEXT function.
