Excel's TEXT function and some other, formula-free methods for converting dates to text

Several methods for doing this in Excel were covered in the prior article. If you need to do the inverse, convert a date from Excel to text, you have a similar selection of options.

As is customary, we'll start with a formulaic answer and then consider some alternative approaches.

Excel's TEXT function was developed to help you display numerical data in the format of your choosing as a string of text.

Excel's TEXT function has the following syntax:

Value in TEXT(format_text)

Where:

• value is a number that needs to be written out. A cell reference, a number, or a formula that returns a number are all acceptable options.
• format_text This is the desired format for the resulting text value, and it should be supplied as a string of text within quotation marks.

The following formula, for instance, will take a date entered in cell A1 and output it as a string of text in the common US date format (month/day/year):

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

The first telltale sign that you're dealing with a textual representation of a date is that the value returned by the TEXT formula is aligned to the left, as shown in the preceding screenshot. Excel provides a number of visual cues beyond cell alignment that allow you to tell the difference between dates and text strings.

The TEXT function in Excel has no trouble converting dates to text values because dates are serial numbers. The most difficult part is likely to be choosing the correct date format for display in the text.

The following date formats are recognized by Microsoft Excel:

Months:

• m = month number, excluding January
• Zero-based month indicator (mm)
• mmm is an abbreviation for the full name of a month, such as "March."
• mmmm - abbreviation for "month, day, and year," in this case "March"
• mmmmm - month initial, as in M (representing March and May).

Days:

• d = the number of days, excluding the first zero
• dd is shorthand for "day of the year"
• day-of-week abbreviation (ddd): Sunday, for instance
• full name of the day of the week, such as "Sunday" in the format dddd

Years:

• year, two digits (yy)
• yyyy - year with four digits

You can customize the appearance of the converted text date by using different delimiters like a dash (-), slash (/), comma (,), colon (:), etc. to separate the date codes. Some instances are as follows:

• U.S. date format "mm/dd/yyyy" shows as 03/08/2015.
• The internationally standard date format, "dd/mm/yyyy," shows 08/03/2015.
• To avoid any confusion, the format "dd-mmm-yy" is displayed as "08-Mar-15."
• Date formatted as "dddd, mmmm d, yyyy," showing the entire date, including the day of the week, as Sunday, March 08, 2015

So, if you want to export a column of US dates from Excel to a As a courtesy to your UK-based business partner, you can change the date formats in your.csv file by following these steps:

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

The following are some additional examples of formulas and the results they produce:

You've used the following time codes in the format_text argument of the Excel TEXT function if your date entries also show times and you want to convert them to text strings without any formatting changes.

Hours:

• hours (h) 0-23 (no leading zero)
• hh - hours that start with a zero, like 00-23

Minutes:

• m - minutes, as in 0–59 with no leading zero.
• minutes (mm): minutes followed by a zero, as 00:59.

Seconds:

• s = seconds, excluding the initial zero
• ss - zero-based seconds

Daily Time Frames:

• Time of Day Displays as AM or PM
• When no other time format is given, 24 hour time is assumed.

You may be wondering how Microsoft Excel tells the difference between the m codes for months and the m codes for minutes. You can tell Excel to show minutes instead of months by placing an "m" after h codes (hours) or before s codes (seconds). That's right, it's that easy:)

Examples of using Excel's TEXT function with its format_text argument include:

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

To convert just the time information, enter the time codes only, as shown here:

A2="h:mm AM/PM" =TEXT

Your TEXT formulas' output might look something like this:

In Excel, you can use the TEXT function to convert the current date to text format by combining it with the TODAY function, which returns the current date.

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

The date of March 8, 2015 would be the answer to this equation. See the date codes discussed in Example 1 if you'd like to present the final string of text in a different format.

Though Excel's TEXT function is primarily meant to transform numerical data into text, it can also do the inverse. e edit for current date The TEXT formula only requires the addition of the double negation (--) to accomplish this.

Using the below formula, you can change the format of cell A1 so that it reads like a date, for instance.

=--TEXT(A1,"mm/dd/yy")

You saw how the TEXT function in Excel converts dates to text effectively. However, if you're not a fan of Excel formulas, this alternative may be more to your liking.

If you read the first installment of our Excel dates tutorial, you know how to convert text to dates with the help of the Text to Columns feature. The only difference between this and the date conversion process is the final step of the wizard, where you'll select Text rather than Date.

You can skip ahead to the next solution that allows you to convert dates to text strings in any format of your choosing if the standard date format is not what you need.

Follow these steps if you don't mind using the standard layout:

1. To convert multiple dates to text in Excel, select the cells containing the dates to be edited.
2. Click Text to Columns in the Data Tools sub-group of the Data tab.
   
3. In the first screen of the wizard, choose the "Delimited" format.
   
4. Click Next on Step 2 of the Wizard without checking any delimiter boxes.
   
5. Select Text under Column data format on the third and final step of the wizard, then press Finish.

Wow, that was simple, right? You can see the outcome in the screenshot below, which shows dates converted to text strings using the short date format determined by your Windows Regional settings (in my case, "mm/dd/yyyy").

Using Notepad or another text editor is another quick, formula-free option for converting Excel date ranges to text strings. Excel date can be converted to text in any format you like with this tool, unlike the Text to Columns wizard.

1. You can format the dates in Excel to match the desired appearance of the text strings.
2. To convert multiple dates at once, simply check the boxes next to them. Ctrl C to imitate them
   
3. To save the copied dates, open Notepad or another text editor and click the "Paste" button.
4. Dates are converted to text format automatically in Notepad. Press Ctrl A to pick out all of the words, and then Ctrl C imitate them
5. Returning to Excel, pick the column into which you'll be dropping the text strings, and formatting it as Text will do the trick. Please use the Ctrl 1 to bring up the Format Cells dialogue, click the Numbers tab, and then choose Text.
   
6. Once you've decided on the first cell to insert the text strings into, you can press Ctrl V stick them

Column B contains the original Excel dates, while column D contains the text entries. Note that the converted text strings are left-highlighted, as should be the case with all text values in Excel, and otherwise accurately reflect the original date format.

Follow these steps to transform Excel dates into text format: We will examine some Excel functions for dealing with weekdays and yearly calendar days next week. And while you wait, you might want to review the earlier sections of our in-depth guide to manipulating dates and times in Excel.