How to Convert Text to Time Format with AM/PM in Excel (3 Methods)

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.


Download Practice Workbook

Download the following workbook to practice by yourself.


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.

excel text format time am pm


3 Methods to Convert Text to Time Format with AM/PM in Excel

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")

Use TEXT Function to Convert Text to Time Format with AM/PM in Excel

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.

Read More: How to Format Text in Excel Cell (10 Approaches)


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.

Convert Text to Time Format with AM/PM in Excel with Format Cells Feature

  • Subsequently, select the Number Format icon which you’ll find in the Number group under the Home tab.

Convert Text to Time Format with AM/PM in Excel with Format Cells Feature

  • 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.

Read More: Excel VBA: Format Cell as Text (3 Methods)


Similar Readings


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)

Apply TIMEVALUE Function for Converting Text to Time Format with AM/PM

  • Afterward, press Enter and use the AutoFill tool to complete the series.

Apply TIMEVALUE Function for Converting Text to Time Format with AM/PM

  • 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.

Read More: How to Add Text after Number with Custom Format in Excel (4 Ways)


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.


Related Articles

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo