Convert date to text in Excel - TEXT function and no-formula ways

This article explores several methods for converting Excel dates into text strings, offering both formula-based and non-formula solutions.

Traditionally, we start with a formula solution and then explore a couple of non-formula alternatives.

• Using the Excel TEXT function
• Employing the Text to Columns feature
• Converting dates to text strings via Notepad

Using the Excel TEXT Function

The Excel TEXT function is purpose-built for converting numeric values into text strings, formatted as specified. Its syntax is:

TEXT(value, format_text)

Where:

• value: The numeric value (number, formula result, or cell reference) to convert.
• format_text: The desired text format, enclosed in quotation marks.

For example, =TEXT(A1,"mm/dd/yyyy") converts the date in cell A1 to a US date format (month/day/year). The left alignment of the result indicates a text string.

Example 1: Converting Dates to Various Text Formats

Excel's TEXT function handles date conversions effectively. The key is using the correct formatting codes:

Month Codes:

• m: Month number (no leading zero)
• mm: Month number (with leading zero)
• mmm: Abbreviated month name (e.g., "Mar")
• mmmm: Full month name (e.g., "March")
• mmmmm: First letter of the month (e.g., "M")

Day Codes:

• d: Day number (no leading zero)
• dd: Day number (with leading zero)
• ddd: Abbreviated day of the week (e.g., "Sun")
• dddd: Full day of the week (e.g., "Sunday")

Year Codes:

• yy: Two-digit year
• yyyy: Four-digit year

Examples:

• "mm/dd/yyyy": US date format (e.g., "03/08/2015")
• "dd/mm/yyyy": International date format (e.g., "08/03/2015")
• "dd-mmm-yy": (e.g., "08-Mar-15")
• "dddd, mmmm d, yyyy": Full date (e.g., "Sunday, March 08, 2015")

Example 2: Converting Time to Text Strings

To include time, use these codes:

Hour Codes:

• h: Hours (0-23, no leading zero)
• hh: Hours (00-23, with leading zero)

Minute Codes:

• m: Minutes (0-59, no leading zero)
• mm: Minutes (00-59, with leading zero)

Second Codes:

• s: Seconds (no leading zero)
• ss: Seconds (with leading zero)

AM/PM: AM/PM

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

Example 3: Converting the Current Date to Text

Use TODAY() with the TEXT function: =TEXT(TODAY(), "dd-mmm-yyyy")

Example 4: Converting Text to Date (Reverse Conversion)

While primarily for number-to-text conversion, the TEXT function can reverse this with double negation (--): =--TEXT(A1,"mm/dd/yy") (Remember to format the cell as a date afterward).

Converting Dates to Text with Excel's Text to Columns Wizard

A formula-free alternative is the Text to Columns wizard. This converts dates to text using the default short date format. If this format suffices:

1. Select dates.
2. Go to Data > Text to Columns.
3. Choose Delimited, then Next.
4. Ensure no delimiters are checked, Next.
5. Select Text as the column data format, Finish.

Converting Excel Dates to Text via Notepad

For complete control over the format, use Notepad:

1. Format dates in Excel.
2. Copy dates (Ctrl C).
3. Paste into Notepad.
4. Copy from Notepad (Ctrl C).
5. Paste into Excel (Ctrl V), ensuring the destination column is formatted as Text.

This article provides various methods to convert Excel dates to text, catering to different preferences and needs.

The above is the detailed content of Convert date to text in Excel - TEXT function and no-formula ways. For more information, please follow other related articles on the PHP Chinese website!