Very often we have to input Time in the Excel worksheet for many different purposes. But we may forget to set the format or change the format from other number formats. And also, we may want to view the time in 12-Hour format with AM/PM. In this article, we will show you the simple yet effective methods to Convert Text to Time Format with AM/PM in Excel.
Dataset Introduction
To illustrate, we are going to use a sample dataset as an example. For instance, the following dataset represents the Salesman of a company and their Entry Time in the office. The Time in Text format is already given here and we’ll show you the ways to convert them to the Time format.
How to Convert Text to Time Format with AM/PM in Excel: 3 Methods
1. Use TEXT Function to Convert Text to Time Format with AM/PM in Excel
Excel provides many different Functions and we use them to perform numerous operations. One useful function of such kind is the TEXT function. The TEXT function transforms a value to a certain number format which is specified by the user. In our first method, we’ll use this function to change the number formats. Therefore, follow the steps below to perform the task.
STEPS:
- First, select cell D6.
- Then, type the formula:
=TEXT(C6,"h:mm:ss AM/PM")
Here, in the argument section, h:mm:ss denotes Hour, Minute, and Second.
- After that, press Enter.
- Finally, use the AutoFill tool for converting the rest. Hence, you’ll get your desired formatting.
2. Convert Text to Time Format with AM/PM in Excel with Format Cells Feature
Besides Functions, Excel also offers various useful Features. The Format Cells feature in Excel helps us to Edit the fonts, alignments, and borders, or to change the number format. In this method, we will use the Format Cells feature to Convert Text to Time Format with AM/PM in Excel. So, learn the following process to carry out the operation.
STEPS:
- Firstly, select the range C5:C10 which has Time in Text format.
- Subsequently, select the Number Format icon which you’ll find in the Number group under the Home tab.
- As a result, the Format Cells dialog box will pop out.
- There, under the Number tab, select Time from Category and choose the desired Time format you want.
- Then, press OK.
- In the end, you’ll get the Time in your required format with AM/PM.
3. Apply TIMEVALUE Function for Converting Text to Time Format with AM/PM
Moreover, we can apply the TIMEVALUE function to create a formula that converts the Text  Time. The TIMEVALUE function basically transforms Time in Text format to an Excel serial number for a time that is understood by Excel. We then need to change the format. Hence, follow the below process to perform the task.
STEPS:
- First of all, select cell D6.
- Then, type the formula:
=TIMEVALUE(C6)
- Afterward, press Enter and use the AutoFill tool to complete the series.
- Now, select the range D6:D11.
- After that, select Time from the Number Formats drop-down list.
- Eventually, it’ll return the Time format with AM/PM.
Download Practice Workbook
Conclusion
Henceforth, you will be able to Convert Text to Time Format with AM/PM in Excel using the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
