To turn a date into text, use the TEXT function with a format code as the second argument: =TEXT(A1,"dd-mmm-yyyy") gives 15-Jan-2026, =TEXT(A1,"mmmm") gives January, and =TEXT(A1,"dddd") gives Thursday. The format code goes in double quotes and controls exactly how the date reads — d/dd for the day number, ddd/dddd for the day name, mmm/mmmm for the month, and yy/yyyy for the year.
Join the pieces with & to build a sentence: ="Due on "&TEXT(A1,"dddd, d mmmm yyyy"). The important consequence is that the result is genuinely text, not a date — it can’t be sorted chronologically, filtered by date, or used in any date calculation, and a column of TEXT results will sort April before January.
So use it for labels, reports, and concatenated strings, and if you only want the date to look different while staying a real date, apply a custom cell format instead (Ctrl+1 → Custom), which changes the display and leaves the underlying value intact.
As you know, Excel stores a date as a number, and you have the date format on the cell to show it as a date. By converting the date into a text, you can get the day name, day number, month name, and year number.
For this, the best way is to use the TEXT function. This function allows you to extract a part (day, month, and year) from a date as a text. In this tutorial, we will look at a few examples to understand how to use this method.
Convert a Date into Text using the TEXT Function
You can use the below steps:
- First, enter the TEXT function in a cell.
- After that, in the first argument, refer to the original date.
- Next, in the second argument, enter the format of the date that you want to get in the result.
- In the end, enter a closing parenthesis and hit Enter to get the result.
=TEXT(A1,"dd-mmm-yy")
For a date like 8-May-2024, this formula returns the text 08-May-24. You can use any date format code in the second argument, like “yyyy-mm-dd” for 2024-05-08:
=TEXT(A1,"yyyy-mm-dd")
Get Month Name as a Text from a Date
In the TEXT function, you need to refer to the actual date in the first argument, and then in the second argument, you need to specify the format of the date that you want to get in the result.
In this formula, we have used the format “MMMM”. This format tells the TEXT function to only get the full month name from the date as a text.
=TEXT(A1,"MMMM")
And if you want to get the short month name, you just need to change the format from “MMMM” to “MMM”.
=TEXT(A1,"MMM")
Get Day Name as a Text from a Date
And if you want to get the day name as a text from the date, you need to use the same function, i.e., TEXT.
Here you have used the same function with the format to get the full day name.
=TEXT(A1,"DDDD")
To get the short day name, you can use the following formula.
=TEXT(A1,"DDD")
Get a Date in a Complete Text Sentence
In the below formula, we have used the TEXT function to create a text sentence to define the date and day name in long sentence form.
="The date is "&TEXT(A1,"DD, MMMM'YY")&" and day is "&TEXT(A1,"DDDD")&"."
For 8-May-2024, this returns “The date is 08, May’24 and day is Wednesday.” The apostrophe in the format code shows as it is, so you don’t need quotes around it.
Why You Need TEXT When Joining a Date with Text
If you join a date with text using the & operator without TEXT, Excel uses the date’s serial number. For example, the formula below returns “Due on 45420” instead of the date:
="Due on "&A1
Wrap the date in TEXT to keep it readable, and the formula returns “Due on 08-May-2024”:
="Due on "&TEXT(A1,"dd-mmm-yyyy")
Convert Dates to Text without a Formula
If you want to change the dates in place, use Text to Columns:
- First of all, select the cells with the dates.
- After that, go to Data ➜ Text to Columns and click Next twice.
- In the end, select Text under Column data format and click Finish.
Excel turns each date into text, as it’s shown in the cell. Keep in mind that you can’t use these text dates in date calculations anymore.
Things to Keep in Mind
- The result is text: it sorts alphabetically, and date functions and formulas can’t use it as a date.
- Blank cells: TEXT treats an empty cell as 0 and returns a date in January 1900, so check the cell first (formula below this list).
- Language of the codes: the format codes depend on the language of your Excel, so a workbook with “dd-mmm-yy” may show the code instead of the date in another language.
=IF(A1="","",TEXT(A1,"dd-mmm-yy"))
Frequently Asked Questions
How do I convert the text back into a date?
Use DATEVALUE or VALUE on the text, like =DATEVALUE(B1), and apply a date format to the result. This works when the text is in a date format your Excel can read.
Are the format codes case-sensitive?
No. “MMMM” and “mmmm” both return the full month name, and “DDDD” and “dddd” both return the full day name.
Why does my result show 00-Jan-00?
The cell you refer to is empty or has 0, which Excel reads as day 0 of January 1900. Use the IF version above to return a blank instead.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.