How to convert Date to text in Microsoft Excel

The conversion of the Date to Text is termed to be very simple as well as the easiest task to achieve in Microsoft Excel. And there are various simple methods that can be efficiently used to convert the dates to Text in Excel as well:

1. What do you mean by text function in Microsoft Excel?
2. Making use of the TEXT function for converting the Date to Text.
3. Convert the Date to the Text by using the Text to the column.
4. By making use of the copy-paste method.
5. What important things need to be remembered while using the Text function in Microsoft Excel?

What do you mean by text function in Microsoft Excel?

It was well known that, in Microsoft Excel the TEXT function is primarily used to convert particular numbers from the numerical format to text format by making use of the formatting codes.

And we can also use it to display the numbers as words, including symbols, while maintaining the numeric value. Moreover, the TEXT function in Excel can also help us to concatenate the numbers to formatted text strings or symbols.

* For example: =TEXT (A6, "dd mmmm, yyyy") to convert the given numerical Date, that is 27-01-2023, into the text formatas 27 January 2023:

Key Highlights regarding the Text Function:

1. We can make use of the TEXT Function to convert the Date into a specific format.
2. And the fractional format is not supported by the TEXT function in Excel.
3. For combining the numbers with the characters or the Text in an excel sheet, we can also make use of the TEXT function.
4. Moreover, we cannot combine the time, Date, #, and 0 in the TEXT function formula.
5. And Microsoft Excel gives an error #Name if no quotation marks are used around the 'format_text' respectively.

The syntax for the TEXT Function

The syntax that is used for the TEXT function in Microsoft Excel is as follows:

The arguments related to the TEXT Function in Excel are as follows:

1. Value (required): The value in the TEXT Function usually indicates the numeric value which we want to convert to Text, and whose value can be a number reference to a cell containing a numeric value as well, or can make use of other function which can be effectively used to returns a number.
2. format_text (required): The format_text arguments in the TEXT function are a format which we want to apply, and we must provide the argument as format code enclosed in quotation marks.

How can one make use of the TEXT Function in Microsoft Excel?

Let us now consider the following examples in order to understand better the use of the TEXT function in Microsoft Excel:

# Example 1: How can one convert Date to text strings in various other formats?

We all know that, in Microsoft Excel, the dates are present in the serial numbers as of their nature, and the Excel TEXT function has no problem converting them to text values.

And the most challenging part is in specifying out the proper display format for the Text dates degrees.

And Microsoft Excel can explicitly understands the below-following date codes.

1) Months:

• m: It can be used for the month number that is leading without a zero.
• mm: It can be used for the month number with a leading zero in excel sheet.
• mmm: It is the short form of the month name for example, Jun.
• mmmm: And it is the full form of the month name, for example, June.
• mmm: In this, we use the month as the first letter; for example, J(for June and July).

2) Days:

• d: This is the day's number without leading to a zero.
• dd: And it is used for the day number with a leading zero.
• ddd: It is extended as the day of the week, for example, Mon.
• dddd: It is the full name of the day of the week, for example, Monday.

3) Years:

• yy: The double represents the two-digit year.
• yyyy: It could be the our-digit year as well.

And to display the converted text date exactly in the way we want, we can separate the date codes with different delimiters available in Microsoft Excel like:

1. Dash (-).
2. Slash (/).
3. The comma (,), etc.

Here we have a few examples of it as well:

1. "mm/dd/yyyy" is the date format widely used in the USA and displays as 03/08/2016.
2. "dd/mm/yyyy" is the date format that can be used by the rest of the world and displays it as 03/08/2016.
3. "dd-mmm-yy" - it displays as 06-Apr-16to avoid any confusion as well.
4. "dddd-mmmm-yyyy" - It is the full Date that includes the day of the week.

• For example: If we are assuming a column of having the US dates in Microsoft Excel, then firstly we need to export them to a .csv file for our UK-based partner, and after that converting the dates to the UK format by making use of the below formula:

=TEXT (A1, "mm/dd/yyyy")

# Example 2: How to convert given time to text strings in Microsoft Excel

If our date entries in an excel sheet are primarily displaying the dates as well as the times and we want to change them to text strings exactly as they are, in that case we can include the following time codes in the format_text argument of the Microsoft Excel TEXT function.

1) Hours:

• h: hours have been written out without leading a zero as 0-23.
• hh: hours have been written with a leading zero as 00-23.

2) Minutes:

• m: minutes those are written without leading a zero as 0-59.
• mm: minutes those are written with leading a zero as 00-59

3) Seconds:

• s: seconds those are written without leading a zero.
• Ss: seconds those are written with a leading zero as well.

Periods of the day:

• AM/PM: It usually displays as AM or PM.
• And if it is not specified, then the 24-hour time format is used effectively.

Moreover, the TEXT functions in Microsoft Excel primarily allow us to include both Dates as well as the time codes in the format_text argument.

• For example:

=TEXT (A2," dd/mm/yyyy hh: mm")

And if in case we want to convert the time portion only, then we can achieve this by just putting only the time codes:

=TEXT (A2, "h: mm AM/PM")

And the results of our TEXT formulas may look much similar to this:

Conversion of the Date to the Text by making use of the Text to column

Now in this, we will be considering the dataset with the various dates from the month of November, as depicted below, and we need to convert these dates into the Text as well:

Below are the mentioned steps that can be used to convert the date into Text as well:

Step 1: In this step, we need to first to select the Date and move to "Data" after that moving to the "Data Tools" and then to Text to Column in Microsoft Excel:

Step 2: After that, we need to click on the "Next" options and also we will be putting uncheck marks on all the "Delimiters."

Step 3: And now, in this step, we will be selecting the location, and then we will click on the "Finish" option as follows:

Step 4: Now, after performing all the above steps, we will be getting the output as follows:

Conversion of the Date to the Text by making use of the Copy paste method

We will again considering out the data consisting of the "date of joining."

Step 1: First, we need to copy down all the data, and then we will open a notepad and paste it there. And as soon as we will be pasting the dates into the notepad, it will get automatically converted into the Text.

Step 2: Now after that, we will switch back to Excel, and then we will be selecting out the cells where we want to paste these dates as well.

Step 3: And then, we must need to go to the "Home" and "Number" with the cells selected, and after that, we will be selecting the "Text" format (from the drop-down) respectively.

Step 4: And at the last, we will be pasting out the Date as Text:

What important things need to be remembered while using the Text function in Microsoft Excel?

The important points as well as things that are required to be remembered by an individual while dealing with the TEXT Function in Microsoft Excel are as follows:

1. It is obvious that the Text function in Microsoft Excel does not convert the numbers from 123 formats to "One Two Three," and in order to achieve this, an individual is required to make use of the Visual Basics for Application (VBA).
2. And in Microsoft Excel, the Text function converts a numeric value to a formatted text; therefore, we cannot make use of the obtained result for the calculation as well.
3. Moreover, the 'format_text' argument in the text function formula does not contain an asterisk character (*).
4. And the TEXT function is available in the following versions of Microsoft Excel which are as follows:
   1. Microsoft Excel 365.
   2. Microsoft Excel version 2019.
   3. Microsoft Excel version 2016.
   4. Microsoft Excel version 2013.
   5. Microsoft Excel version 2011 for Mac Excel 2010, etc.