How to convert date to text in Excel (Easy Formula)

In Excel, the date and time are kept as numbers. A user may now utilise these dates and times in computations thanks to this. You may, for instance, extend a given date by a certain amount of days or hours.

But occasionally you would like these dates to function more like text. In these circumstances, you must understand how to convert a date to text.

Converting dates to text in Excel using the TEXT function:

A numeric number can be turned into a text string and displayed in the format you define using the Excel TEXT function.

The syntax for the Excel TEXT function is as follows:

Where: TEXT(value, format text)

You wish to turn the numeric number value into text. This might be a numeric value, the result of a formula, or a pointer to a cell that has a numeric value.

The formatted text value should be given as a text string contained in quotation marks and named format text.

For instance, you may use the formula below to turn a date in cell A1 into a text string using the standard month/day/year format used in the US:

=TEXT(A1,mm/dd/yyyy)

The value given by the TEXT formula is oriented to the left, which is the first indicator that points to a date that has been formatted as text, as you can see in the screenshot up above. There are a few other signs in Excel that might help you differentiate between dates and text strings in addition to alignment in a cell.

Example 1: Different formats of date conversion to text strings:

Excel's TEXT function has no trouble converting dates to text values since by definition dates in Excel are serial numbers. Choosing the appropriate display formatting for the text dates is perhaps the most difficult step.

Excel understands the following date codes.

Months:

month number without a leading zero is m

month number with a leading zero is mm

mmm - abbreviated form of the month name, for example Jan

mmmm - full form of the month name, for example january

mmmmm - use the month as the first letter, like M (stands for March and May)

Days:

days without a leading zero is d

Day number with a leading zero is dd.

ddd is the abbreviation for the day of the week, such as Mon.

dddd is the complete name for the day of the week, such as Monday.

Years:

yy - two-digit year

yyyy - four-digit year.

Example 3. Excel's current date convert to text

The Excel TEXT function may be used in conjunction with the TODAY function, which returns the current date, if you wish to convert the current date to text format, like in the following example:

=TEXT(TODAY(), dd-mmm-yyyy)

Using Excel's Text to Columns wizard to convert a date to text:

Excel's TEXT function does a good job of converting dates to text, as you've just seen. However, if you dislike using Excel formulae, you might prefer this option.

You already know how to use Text to Columns to convert text to date if you had a chance to read the first section of our Excel dates guide. The only difference is that you select Text rather than Date on the wizard's last step if you want to convert dates to text strings.

Take the following actions:

Select all of the dates you wish to convert to text in your Excel file.Locate the Data Tools group on the Data tab and select Text to Columns.Click Text to Columns after switching to the Data tab.Select the Delimited file type on the wizard's step 1 and then click Next.

Select Delimited on the wizard's step 1 and then click Next.Make sure none of the delimiter boxes are ticked on step 2 of the wizard, then click Next.

Uncheck every delimiter box on the wizard's step 2 and then click Next.Select Text under Column data format on the wizard's third and last step, then click Finish.

That was quite simple, right? The picture below shows the outcome - dates converted to text strings in your Windows Regional settings' default short date format, which in my case is mm/dd/yyyy:

Did you learn about how to covert date into text in Excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience