Learn To Convert Date To Text In Excel With 3 Easy Methods

Do you need help converting dates into text within Excel to make your work easier and more efficient?

If so, look no further than this blog post! We'll teach you how to convert any date format into plain text.

So if you're struggling with data entry and want to turn dates into high-quality formatted text, keep reading as we cover the following:

• Convert Date to Text with the TEXT Function

• Convert Date to Text with Text to Column Feature

• Use the Copy-Paste Method Converting Date to Text in Excel

• Final Thoughts on Converting Date to Text in Excel

• Frequently Asked Questions on Convert Date to Text in Excel

Convert Date to Text with the TEXT Function

The TEXT function is most suitable for displaying values in a particular format, such as presenting a date (a numeric value) in the desired date format.

Text function syntax

The following image below is the representation of the TEXT function syntax:

The TEXTfunction requires two arguments to convert dates to text, and these are the following:

• Value- is the numerical value you want to convert to text. A value can be a number, cell reference, or formula that retrieves a numerical value.

• Format Text - Format Text specifies the retrieving specific format, whether text format, including as a text string in enclosed quotation marks.

When using the TEXT function, it's important to understand the available formatting options.

The format consists of four parts for dates:

• day format

• month format

• year format

• separator

Converting Excel dates to text values using the TEXT function is easy because the dates are serial numbers.

However, the tricky part is selecting the correct display format for displaying the text dates.

Months:

• "m" represents the month number without a leading zero.

• "mm" represents the month number with a leading zero.

• "mmm" represents the short form of the month name, e.g., Jun for June.

• "mmmm" represents the full form of the month name, e.g., June for June.

• "Mmmmm" represents the first letter of the month, e.g., J for June and July.

Days:

• "d" for the day number without a leading zero

• "dd" for the day number with a leading zero

• "ddd" for the abbreviated day of the week, such as "FRI"

• "dddd" for the complete name of the day of the week, such as "Friday"

Years:

• "yy" is two-digit for the year.

• "yyyy" four-digit for the year.

To customize the format of the converted text date, you can use different delimiters like dash (-), slash (/), comma (,), and colon (:) with the given date and codes.

• The date format used in the USA, "mm/dd/yyyy," is displayed as "06/04/2023".

• The date format displayed as "08/04/2019" is in the "dd/mm/yyyy" format used by the rest of the world.

• To avoid confusion, the date format "dd-mmm-yy" will be displayed as "08-Jun-15".

• The date June 8, 2015, a Monday, is displayed as "Monday, June 08, 2015" or "dddd, mmmm d, yyyy."

Convert Dates to Text with the TEXT Function in Excel

1. Type the TEXT function, the cell reference, and then choose a date format.

2. Press Enter to get the Text value or the result.

The TEXT function converted the cell reference with the current date into text using the specified format.

Convert Date to Text with the Text to Column Feature

You can try the Text to Column feature to convert dates to text in Excel without using formulas.

In our example, we have a dataset and want to convert the dates to text format:

How to convert date to text with the Text to Column feature

These are the steps to convert date to text with the Text to Column conversion feature:

1. Choose the cells you want to convert to text.

2. Go to the Data tab, then click the Text to Columns wizard feature under the Data Tools.

3. Choose Delimited and click the Next button.

4. Uncheck all delimiters and click the Next button.

5. Under the Column Data format, select Text, choose a destination cell for your converted date, and click the Finish button.

By applying the steps above, the dates will turn into text format.

Note: 

• The dates in the two columns have different formats. The original format was dd mmm, yyyy, but the result shows dd-mm-yyyy.

• It's important to note that the Text to Column feature will convert dates to the default short date format, which is dd-mm-yyyy based on my system's regional settings.

Use the Copy-Paste Method when Converting Date to Text in Excel

The Copy and Paste method is the quickest way to convert dates to text.

1. Select and copy your dates.

2. Open your Notepad and paste the dates.

3. Select all the dates, then copy.

   

4. Select a column you want to put your text values.

5. Right-click the selected cells, and choose the Format Cells option.

6. Go to the Number format tab in the Format Cells dialog box, choose "Text," and click OK.

7. Press the CTRL + V keys to paste the text values.

This screenshot displays the outcome of converting Excel dates stored in column A and text entries in column B.

The converted text strings match the original date format completely, except for the left alignment error, which is typical for Excel text values.

Final Thoughts on Convert Date to Text In Excel

The steps to convert a date to text in Excel are straightforward. Remember that when converting a date to text, ensure the cell contains a date format. Otherwise, Excel won't recognize it as a date.

Visit Simple Sheets for more easy-to-follow guides and examples, and remember to read the related articles section of this blog post.

For the most straightforward Excel video tutorials, subscribe to Simple Sheets on Youtube!

Frequently Asked Questions on Convert Date To Text In Excel

How can I convert a date value to text without losing its formatting in Excel?

To convert a date value to text without losing its formatting, follow the steps below:

1. Select your date value

2. Go to the Home tab

3. Under the Number group, choose Text format from the drop-down.

Why can't I change my date format?

You can't change your date format because it is a text value. The date value must be real numbers and not text values.

Why does Excel keep formatting date values in my spreadsheet?

When using Microsoft Excel, dates are automatically modified for easier data entry. This can be frustrating if you want to keep a specific entry the same.

Related Articles:

Beginners Guide: How To Insert Column Charts In Excel

Arrow Keys Not Working In Excel? Here's How to Fix It

Apple Numbers Formulas: How To Use Them Effectively