In the previous article, we discussed different ways to convert text to date in Excel. If you are looking for a solution to the opposite task - changing an Excel date to text - a few choices are available to you again.

Traditionally, we'll begin with a formula solution and then explore a couple of non-formula ways.

The Excel TEXT function is specially designed to convert a numeric value to a text string and display it in the format you specify.

The syntax of the Excel TEXT function is as follows:

TEXT(value, format_text)

Where:

`value`

is a numeric value you want to convert to text. This can be a number, a formula that returns a numeric value, or a reference to a cell containing a number.`format_text`

this is how you want to format the resulting text value, provided as a text string enclosed in quotation marks.

For example, you can use the following formula to convert a date in cell A1 to a text string in the traditional US date format (month/day/year):

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

As you see in the screenshot above, the value returned by the TEXT formula is aligned to the left, which is the first sign that points to a date formatted as text. Apart from alignment in a cell, there are a few more indicators that can help you distinguish between dates and text strings in Excel.

Since Excel dates are serial numbers in their nature, the Excel TEXT function has no problem with converting them to text values. The most challenging part is probably specifying the proper display formatting for the text dates.

Microsoft Excel understands the following **date codes**.

**Months**:

- m - month number without a leading zero
- mm - month number with a leading zero
- mmm - short form of the month name, for example
*Mar* - mmmm - full form of the month name, for example
*March* - mmmmm - month as the first letter, for example
*M*(stands for March and May)

**Days:**

- d - days number without a leading zero
- dd - day number with a leading zero
- ddd - abbreviated day of the week, for example
*Sun* - dddd - full name of the day of the week, for example
*Sunday*

**Years:**

- yy - two-digit year
- yyyy - four-digit year

To display the converted text date exactly the way you want, you can separate the date codes with various delimiters such as dash (-), slash (/), comma (,) colon (:), etc. Here are a few examples:

- "mm/dd/yyyy" - the date format used in the USA, displays as
*03/08/2015* - "dd/mm/yyyy" - the date format used by the rest of the world, displays as
*08/03/2015* - "dd-mmm-yy" - displays as
*08-Mar-15*to avoid any confusion : ) - "dddd, mmmm d, yyyy" - full date, including the day of the week, displays as
*Sunday, March 08, 2015*

For example, if you have a column of US dates in Excel and you need to export them to a .csv file for your UK based partner, you can convert the dates to the UK format, as a courtesy:

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

Some more formula examples and their results are shown below:

If your date entries display both dates and times and you want to change them to text strings exactly as they are, you included the following **time codes** in the format_text argument of the Excel TEXT function.

**Hours**:

- h - hours without a leading zero, as 0-23.
- hh - hours with a leading zero, as 00-23.

**Minutes**:

- m - minutes without a leading zero, as 0-59
- mm - minutes with a leading zero, as 00-59

**Seconds:**

- s - seconds without a leading zero
- ss - seconds with a leading zero

**Periods of the day:**

- AM/PM - displays as AM or PM
- If not specified, 24-hour time format is used

As you probably noticed, the **m** codes are used for months as well as minutes, and you might be curious how Microsoft Excel distinguishes between them. If you put "*m*" immediately after **h** codes (hours) or immediately before **s** codes (seconds), Excel understands you want to display minutes rather than a month. Yep, it's that simple : )

The TEXT function in Excel allows including both date and time codes in the `format_text`

argument, for example:

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

If you want to convert the time portion only, then put only the time codes, like this:

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

The results of your TEXT formulas may look similar to this:

In case you want to convert the current date to the text format, you can use the Excel TEXT function in combination with the TODAY function that returns the current date, for example:

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

The result of this formula would show up as 08-Mar-2015. If you prefer to display the resulting text string in some other format, please see the date codes discussed in Example 1.

Though the main destination of the TEXT function in Excel is converting numbers to text, it can also perform a reverse conversion, i.e. change text to date. For this, you simply add the double negation (--) to your TEXT formula.

For example, to convert a text string in cell A1 to date, you use the below formula, and then format the cell as a date.

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

As you've just seen, Excel's TEXT function makes a good job of converting dates to text. But if you are not a big fan of Excel formulas, you might like this solution better.

If you had a chance to read the previous part of our Excel dates tutorial, you already know how to use *Text to Columns* to change text to date. To convert dates to text strings, you proceed in the same way with the only difference that you choose **Text** instead of *Date* on the final step of the wizard.

If the default date format is not what you are looking for, you can jump right to the next solution that lets you convert dates to text strings in any format of your choosing.

If you don't mind the default format, then perform the following steps:

- In your Excel spreadsheet, select all of the dates you want to change to text.
- On the
*Data*tab, find the*Data Tools*group, and click**Text to Columns.**

- On step 1 of the wizard, select the
**Delimited**file type and click*Next*.

- On step 2 of the wizard, make sure none of the delimiter boxes is checked and click
*Next*.

- On step 3 of the wizard, which is the final step, select
**Text**under*Column data format*and click*Finish.*

That was really easy, right? The screenshot below demonstrates the result - dates converted to text strings in the default short date format set in your Windows Regional settings, which is "mm/dd/yyyy" in my case:

Another quick no-formula way to turn Excel dates into text strings is using Notepad or any other text editor. Unlike the Text to Columns wizard, it allows you to convert Excel date to text in any format of your choosing.

- In your Excel worksheet, format the dates exactly as you want the text strings to look like.
- Select all of the dates you want to convert and press Ctrl+C to copy them.

- Open Notepad or any other text editor, and paste the copied dates there.
- Notepad automatically converts the dates to the text format. Press Ctrl+A to select all text strings, and then Ctrl+C to copy them.
- Switch back to Microsoft Excel, select the column where you want to insert the text strings and apply the
**Text**format to it. To do this, press Ctrl+1 to open the*Format Cells*dialog and select**Text**on the*Number*tab.

- Finally, select the first cell where you want to insert the text strings and press Ctrl+V to paste them.

The following screenshot shows the result, with the original Excel dates in column B and text entries in column D. Please notice that the converted text strings reflect the original date format with absolute accuracy, except they are left-alighted, as all text values are supposed to be in Excel.

This is how you convert date to text in Excel. Next week we will explorer a few Excel functions to work with weekdays and days of the year. And in the meantime, you may want to check out the previous parts of our comprehensive tutorial to working with dates and times in Excel.

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

## 167 responses to "How to convert date to text in Excel with TEXT function and without formulas"

It worked for me, Thank you

Hello

I want to use the TODAY() function within functions such as SUMIFS where the date is in column A. For example if TODAY() is in A5 and the excel sheet has numbers in column V and dates in column P. The formula =SUMIFS(V2:V90,P2:P90,"17/12/2020") gives the correct answer but when I try to use the TODAY() function such as =SUMIFS(V2:V90,P2:P90,"=TODAY()") or =SUMIFS(V2:V90,P2:P90,"=A5") all I get is zero. Today's date is 17/12/2020. How do I use the TODAY() function to get the right answer? I need it to be flexible so I can use TODAY()-1, TODAY()-2 etc. as well.

Thank you for any help.

Remove the "=" before "TODAY()" and it should work

How do I convert the number in a date formatted cell into the three letter month abbreviation?

Ex: visually Jan, actual cell contents 1/12/2018, result when I convert to text 43112.

Hello!

If I got you right, check out this article to learn how to extract month name from date in Excel.

I hope it’ll be helpful.

how the date/ month / year ( 03.05.2019) convert into words - third may two thousand and nineteen

Hello!

Here is the article that may be helpful to you: How to convert number to words in Excel

I hope it’ll be helpful.

hi, i want to convert 37.75 to time 37:45 i did this using format cells but it's format include ss eg

37:45:00, i want to convert this to 37:45( hh:mm )in text format , please help..

Hello!

Here is the article that may be helpful to you: Convert text to time using TIMEVALUE function.

You can use this formula:

=TIMEVALUE("0:"&SUBSTITUTE(C1,".",":",1))

I hope it’ll be helpful.

Correction in the main article:

""dd-mmm-yy" - displays as 08-Mar-2015 to avoid any confusion : )"

should read:

"dd-mmm-yy" - displays as 08-Mar-15 to avoid any confusion : )"

Hi David,

You are absolutely right. Fixed, thank you!

need to get an output with"/2021"alongwith the typed value.

eg: if i type 1234 and enter the same cell should show "1234/2021"

Hello!

Try using custom number format

##"/2021"

Hope this is what you need.

Not able to convert 30-04-2021 to text

Hello!

I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

=TEXT(A2,"DD-MM-YYYY")

Hello,

I want to convert yyyymmdd to text but when I do it shanges to 4something. The other issue is that when I double click its (for example) 5/7/2021 I have it as 20210507 and want to take away the dat formula and just keep the number as text and the same way. Please advice...

Hello!

Your task is not completely clear to me. I cannot repeat your problem here. What formula are you using? Your question is not entirely clear, please specify.

Hi!

I tried to use the TEXT function with =TEXT(A1; "dd/mm/yyyy") but the result is "08/03/yyyy". I have tried in multiple date forms for A1, and the chosen format, but the year always shows up as yyyy. If I change to =TEXT(A1; "yyyy/mm/dd"), I get an error in value: "A value used in the formula is of the wrong data type". Do you know what could be happening?

(Side note: If I use the Text to Columns wizard, it works just fine...)

Hi!

What is the value written in A1?

I have used 08/03/2015, 2015/03/08, March 8, 2015,...

I always make sure the formatting is date.

Hello!

I write your values as dates. I was able to repeat your mistake only if the letters yyyy are not letters of the English alphabet, but are written in another language.

Thank you that really helped!! I was assuming that it would be yyyy for any language, but in portuguese it is aaaa (of 'ano')! Thank you so much :)

The year 1950 will be converted as "Nineteen Fifty" In text. For this, can you specify what should I do?

Hello!

You can split your number into 2 cells using the formulas:

LEFT(A1,2) and RIGHT(A1,2)

Then use the recommendations from the article: How to convert numbers to words in Excel.