How to convert time string to time in Excel?

For instance, you have a list of text string in a worksheet, and now you need to convert these text strings to time or date and time, how can you solve it? Here I will introduce some tricks for you to quickly convert text string to time in Excel.

Convert text string to time

Convert text string to date and time

Convert text string to time

To convert text string to time, you can use some formulas to solve.

1. Select a cell and type this formula =TIMEVALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2)) (A1 is the text string you need to convert to time) into it, and press Enter. If you need, you can drag the fill handle to the range you need. See screenshot:

2. Then right click at these selected cells, and select Format Cells from the context menu, and then in the Format Cells dialog, select Time from the Category list under Number tab, then select the time type you need.

3. Click OK, now text strings are converted to time.

Note:

1. This formula cannot work correctly when text string including seconds, such as 120158.

2. If your text string format is 120456P, you can use this formula =TEXT(--(LEFT(A1,LEN(A1)-1)),"0\:00\:00")+((RIGHT(A1,1)="P")/2), then format the cells as the 12 hour clock you need. See screenshot:

Convert text string to date and time

If your text string includes date and time such as 20141212 0312, you can use a little long formula to solve.

1. In the blank cell, type this formula =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,10,2),RIGHT(A1,2),0), and then press Enter key, if you need, you can apply this formula to a range.

2. Then right click at the selected cells and select Format Cells from context menu, then in the Format Cells dialog, select Date from the Category list, and select the type you need from right section.

3. Click OK. Now you can see the text string converted to date and time.

Tip.If you want to convert a time to decimal hours, minutes or seconds, please try to use the Kutools for Excel’s Convert Time as shown in the following screenshot. It’s full function without limitation in 30 days, please download and have a free trial now.

Quickly convert time to decimal value in Excel

For example, you have a list of time needed to be converted todecimal hours, minutes or seconds, how can you quickly and easily solve it in Excel? The Convert Time of Kutools for Excel, can do a favor for you.   Click for 30 days free trial!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Best Office Productivity Tools

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |  Round without Formula ... Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup .... Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List .... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ... Featured Features: Grid Focus   |  Design View   |  Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
This comment was minimized by the moderator on the site
Would love any help with this. I have data that is reported as "1d 01h 01m" representing 1 day, 1 hour, 1 min and I need to be able to represent this as a single number (preferably in # of minutes) any thoughts on how I can convert this string to a number value? Thanks all!
This comment was minimized by the moderator on the site
how to convert 06Hr:11Mi into 06:11
This comment was minimized by the moderator on the site
Hi, abinash kumar, you can remove alpha characters from your time string.