How to convert date to text in Excel with TEXT function and without formulas

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.

Using TEXT function in Excel to convert date to text

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")
Using Excel TEXT function to convert a date to a text string

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.

Example 1. How to convert date to text strings in different formats

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:
Converting dates to text format in Excel using the TEXT function

Example 2. How to convert time to text strings

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:
Text formulas to convert date and time to text strings

Example 3. How to convert the current date to text in Excel

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.

Example 4. Excel TEXT formula to convert text to date

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")
A TEXT formula to convert text to date

Note. In the above examples, we used the date and time codes for the English locale of Excel. If you have a different locale, the codes may be different for your language.

Converting date to text with Excel's Text to Columns wizard

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.

Note. The Text to Column wizard always converts dates in the default short date format regardless of how the original dates are displayed in your worksheet. You can find more about default date and time formats in the following article: Default date format in Excel.

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:

  1. In your Excel spreadsheet, select all of the dates you want to change to text.
  2. On the Data tab, find the Data Tools group, and click Text to Columns.
    Switch to the Data tab and click Text to Columns.
  3. On step 1 of the wizard, select the Delimited file type and click Next.
    On step 1 of the wizard, select Delimited and click Next.
  4. On step 2 of the wizard, make sure none of the delimiter boxes is checked and click Next.
    On step 2 of the wizard, uncheck all delimiter boxes and click Next.
  5. On step 3 of the wizard, which is the final step, select Text under Column data format and click Finish.

Tip. If you don't want the resulting text strings to overwrite the original dates, specify the Destination for the top cell of the new column.
Converting dates to text format using the Convert Text to Columns Wizard

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:
Dates converted to text strings in the default short date format

Convert Excel date to text via Notepad

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.

  1. In your Excel worksheet, format the dates exactly as you want the text strings to look like.
  2. Select all of the dates you want to convert and press Ctrl+C to copy them.
    Copy the dates you want to convert to text.
  3. Open Notepad or any other text editor, and paste the copied dates there.
  4. Notepad automatically converts the dates to the text format. Press Ctrl+A to select all text strings, and then Ctrl+C to copy them.
  5. 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.
    Select the column where you want to insert the text strings and apply the Text format to it.
  6. 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.
The converted text strings look identical to the original dates.

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 Dates Tutorials:

  • Excel date format - explains how to change the default date format in Excel and create custom formatting.
  • How to insert dates in Excel - learn various ways of entering dates in Excel: how to insert the today date and current time, how to auto populate a column or row with weekdays, how to auto fill random dates in Excel, and more.
  • How to convert text to date in Excel - this tutorial demonstrates how you can use Excel functions to convert text to date and how to turn text strings into dates in a non-formula way.
  • How to conditionally format dates in Excel - shows how to apply Excel's presets for dates and set up your own conditional formatting rules based on formulas.

Table of contents

212 comments

  1. 1) Apples- Fruits, $100 (existing typed in one cell)
    How to formula this into 3 dfferent cell to Cell A1,B1,C1

    2) under one cell with 12345
    How can i separate it into Cell A1,B1,C1,D1,E1
    Thanks

  2. how do I convert :

    01/07/2012 to 1st July, 2012

    02/07/2012 to 2nd July, 2012

    03/07/2012 to 3rd July, 2012

    04/07/2012 to 4th July, 2012

    Meaning for:

    01 it is 1st

    02 it is 2nd

    03 it is 3rd

    04 it is 4th

    • Hello!
      To format date with “st”, “nd”, “rd”, “th” you can use the formula

      =CONCATENATE(DAY(D2)&LOOKUP(DAY(D2),{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"})," ",TEXT(D2,"mmmm, yyyy"))

      Day(D2) – returns the day of the month in number
      LOOKUP(DAY(D2),{1,”st”;2,”nd”;3,”rd”;4,”th”;21,”st”;22,”nd”;23,”rd”;24,”th”;31,”st”}) – returns the corresponding suffix of the day
      I hope my advice will help you solve your task.

  3. How to convert "03/25/1997" into "March Twenty-Fifth, Nineteen Hundred Ninety-Seven"

  4. I want to convert the date to complete text like, 1-Jan-2021 to First January Two Thousand and Twenty One.
    Please can you guide in this.

  5. Please help to convert the below mentioned date
    DD-MM-YYYY
    into DD.MM format
    tnx

  6. Please help to convert the below mentioned date Sep 11 2020 11:52AM
    into DD-MM-YYYY HH:MM:SS format

    • Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(MID(A1,18,2)="PM",DATEVALUE(MID(A1,5,2) & "-"&LEFT(A1,3) & "-"&MID(A1,8,4))+TIMEVALUE(MID(A1,13,5))+0.5, DATEVALUE(MID(A1,5,2) & "-"&LEFT(A1,3)&"-"&MID(A1,8,4)) + TIMEVALUE(MID(A1,13,5)))

      Set a custom date and time format in the cell DD-MM-YYYY HH:MM:SS

  7. thanks for your online support it's very helpful.

    Thanks,
    Kumar.S

  8. I am creating a spreadsheet with document name, document type, and date columns. In the final column I am using the "concatenate" function to piece all this data together in order to create a file name (in other words, I'm creating a naming convention for storage of all the files listed on this spreadsheet.

    For sorting purposes, I want the year first, then the month, then the date. I can force the date fields to reflect this by using the custom format option. However, when I use the "text" function to bring that date over into the file name column, the date is still converting into Excel's serial number for that date. (2020.05.05 converts to 43956).

    How can I get the date to carry over properly?

    Here is my formula, with G17 being the date field carrying over into this column:
    =IF(D17=" ",(CONCATENATE(F17,"_",TEXT(G17,"yyyy-mm-dd"),"_",TEXT(H17,"00000000"),"_",I17)),(IF(D17"",(CONCATENATE(F17,"_",G17,"_",TEXT(H17,"00000000"),"_",I17,"_",D17)),(CONCATENATE(F17,"_",G17,"_",TEXT(H17,"00000000"),"_",I17)))))

  9. I would like to convert a text string that contains date format MMM-YY such as "Mar-19" and "Jun-18" to be formatted in MMM-YY format, but I do not want it to assume that the date is of the current year.

    Example:
    Current year is 2020, my cell contains text "Mar-19", but using =Text(A1,"MMM-YY") returns value "Mar-20".
    I would like this to return "Mar-19".

    My hope is to use this text in a subtraction formula to find how many months from this start date to current date.

    • Hello Daniel!
      To convert text "Mar-19" to date you can use this formula

      =DATEVALUE("01-"&A2)

      I hope my advice will help you solve your task.

  10. Thank you !!! It helped me a lot <3

  11. Hi,
    Could you please share me the process to convert below text to date:
    07 May 2020 17:23:47:000

    • Hello Tanvir!
      If I understand your task correctly, the following formula should work for you:

      =DATEVALUE(SUBSTITUTE(A5,TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",20)),20)),"")) + TIMEVALUE(LEFT(TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",20)),20)),8))

      you can try to use Custom Format. Please go to Format Cells, choose Number -> Custom Format and set the followig format:
      dd mmmm yyyy hh:mm:ss
      I hope my advice will help you solve your task.

  12. Hi,I have Mar20. Is it possible to convert it to 3/31/2020, which is the last date of the month?

    Thank you so much if anyone can help.

    • Hello Jenny!
      Please try the following formula

      =EOMONTH(A1,0)

      Note! Don't forget to set the Date format for cell E1.
      Hope you’ll find this information helpful.

      • It works! Thank you so much!

  13. Thanks alot for sharing.

  14. Hai mam,
    in a file some date are entered but file sent to one system to another some date should be shuffled (it have 2000 entries in a column) .
    ie.
    10-03-19
    10-03-19
    10-03-19
    04-10-19
    10-08-19
    04-10-19
    10-10-19
    10-08-19
    in this file "10" represent the month. i want this file in dd/mm/yy format. please support me.

  15. I WANT YYYYMM DATE NOT WORKING THE TEXT COMMAND 199707 20/07/1997 I WANT 199707 HOW I CAN DO

  16. Hi,
    i have the following problem.
    When exporting my contacts from Linkedin, I receive a .csv file. I open the file in Excel (2013). One column of this file is filled with dates that look like this:
    10/27/19, 6:13 AM
    This format is text as it sits on the left of the cell.
    Manually, i delete the comma and the time. In this example i come up with:
    10/27/19
    I can't convert this date with the function DATEVALUE. I have a VALUE ERROR.

    I tried to used the "Text to column" Wizard, no results.

    I think that my problem comes from the fact I have a date:
    in a US format
    AND
    in text format.
    Do you have any idea you to solve this?

    Interestingly, i have a like macro my PC, a datepicker that manages to convert my american text date to a UK date. But this means i would have to click all the dates in my column one by one, which is not convenient.

    Thx

    JG

  17. date show as "20191231".how can convert it as "YYYY/MM/DD"

  18. I am not able to change 10-MAY-89 to another format like yyyymmdd.
    The format of the cell is standard and whenever I try to modify it to any date format, nothing happens.

    Thanks for your help,
    Any

  19. i want the format SEP 19 in csv from 01-09-2019
    date to text format
    how to convert , pls tell me

  20. Hello
    I want to change dight into Text.
    Ex:7:30 to convert into 7hrs30min.
    Please let me as soon as possible.
    Its needed for me!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)