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 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 the following formula (A1 is the text string you need to convert to time):
=TIMEVALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))2. Press Enter. If you need, you can drag the fill handle to the range you need. See screenshot:
3. 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.
4. Click OK, now text strings are converted to time.
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:
 |
 |
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
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.
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
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in