How do I convert a date to text in Excel?

Point TEXT at the date with a date format code: =TEXT(A2,"dd mmm yyyy") returns text like 15 Aug 2026, and Microsoft's own example joins a date into a sentence with =A2&" "&TEXT(B2,"mm/dd/yy"). Without TEXT, joining a date with the ampersand inserts the serial number Excel stores for the date, not a readable date.

Why does my date turn into a number like 39448 when I combine it with text?

The ampersand uses the value the cell stores, not the formatted display, and Excel stores dates as sequential serial numbers: January 1, 2008 is stored as 39448. Wrap the date in TEXT with a format code, for example ="Due "&TEXT(A2,"dd mmm yyyy"), and the label shows the date instead of its serial number.

Why doesn't my TEXT result add up in SUM?

Because the result of TEXT is text, and SUM adds only the numeric values in a range, ignoring text. A column of TEXT outputs therefore totals to 0 with no error anywhere. Keep the original numbers in their own cells, build labels elsewhere, and point SUM at the originals. Microsoft documents two ways to convert text values back to numbers: multiply by 1, as in =D4*1, or use the double unary operator, as in =--D4.

What is the difference between TEXT and Format Cells?

Format Cells changes only how a cell displays: a cell showing 40% can still store the number 0.4, which is what formulas see. TEXT builds a new value that is text in the shape you asked for. Microsoft's TEXT page notes the format can be changed without any formula: press Ctrl+1 (Cmd+1 on the Mac) and pick from the Format Cells Number dialog. Use Format Cells for values that stay in calculations; use TEXT when you need formatted digits inside a text string.

How do I get the month name from a date in Excel?

Use TEXT with a month code: =TEXT(A2,"mmmm") returns the full name, like August, and =TEXT(A2,"mmm") the short form, like Aug. The same pattern returns weekday names with dddd and ddd. Microsoft's format-code table documents mmmm as months shown January through December. The results are text: right for labels and headings, not for arithmetic.

The TEXT function in Excel: format numbers and dates as text

Q: What does the TEXT function do in Excel?

TEXT converts a value to text in a format you specify with a format code: =TEXT(1234.567,"$#,##0.00") returns the text $1,234.57, rounded to two decimal places. It exists for display: readable numbers, and numbers joined with words. The catch is the output type: the result is text, so it no longer behaves as a number in later calculations.

The TEXT function converts a value into text, in a shape you pick with a format code: =TEXT(B2,"$#,##0.00") turns 1234.567 into the text $1,234.57, and ="Due "&TEXT(B2,"dd mmm yyyy") builds a label that reads Due 15 Aug 2026 instead of Due 46249. Microsoft’s one-line description of the function: “The TEXT function lets you change the way a number appears by applying formatting to it with format codes.” The two jobs its page lists are making numbers easier to read and joining them with text or symbols.

The conversion is also the trap. The output of TEXT is text, not a number: it will not sum, and nothing on the sheet tells you so. This page covers the syntax, the format codes worth knowing, the date-label pattern that is the function’s day job, and the ways it goes wrong.

What does the TEXT function do in Excel?

=TEXT(value, format_text)

The first argument is the value to convert, usually a cell reference. The second is the format code, a text string that defines the formatting to apply, and it has to sit inside quotation marks: leave the quotes off and Excel raises an error message instead of a result.

=TEXT(B2,"$#,##0.00")

	A	B	C
1	Item	Amount	Label
2	Order total	1234.567	$1,234.57

TEXT applies the currency code to the number in B2 and returns the text $1,234.57; the output is rounded to two decimal places. B2 itself still holds 1234.567.

The value, format code, and output come straight from Microsoft’s own examples table, and they show the shape of every TEXT call: a raw value goes in, a formatted string comes out, and the original cell is untouched.

The catch: the result is text

Everything TEXT returns is text, including results that look exactly like numbers. Microsoft’s page on combining text and numbers states it flatly: “When you do combine numbers and text in a cell, the numbers become text and no longer function as numeric values.” Arithmetic is over for that value. SUM adds only the numeric entries in a range and ignores text, so a column of TEXT results totals to 0, with no error anywhere to say why. The output also sits left aligned in its cell, the usual tell for text; real numbers sit right aligned.

That is why the advice on the function’s own page is to keep two cells: park the number in one, build the TEXT version in another, and point every later formula at the number, never at the label. TEXT output belongs in labels, headings, and export columns, not in anything that feeds a total.

If a value does need to come back from text, Microsoft documents two conversions: multiply by 1, as in =D4*1, or apply the double unary operator, as in =--D4. The wider story of text-typed numbers, the accidental kind with the green triangles, is covered in numbers stored as text in Excel.

The format codes worth knowing

Each of these is demonstrated on Microsoft’s TEXT or format-code pages; the outputs shown are Microsoft’s own.

Code	What it renders	Example
`$#,##0.00`	Currency, thousands separator, two decimals	`=TEXT(1234.567,"$#,##0.00")` returns $1,234.57
`#,###`	Thousands separator	12200000 renders as 12,200,000
`0.0%`	Percentage, one decimal	`=TEXT(0.285,"0.0%")` returns 28.5%
`0.00E+00`	Scientific notation	`=TEXT(12200000,"0.00E+00")` returns 1.22E+07
`0000000`	Leading zeros, padded to seven characters	`=TEXT(1234,"0000000")` returns 0001234
`mm/dd/yy`	Date	`=TEXT(TODAY(),"mm/dd/yy")` returns a date like 03/14/12
`mmmm` and `mmm`	Month name: January through December, or Jan through Dec	`=TEXT(TODAY(),"mmmm")` returns a month like August
`dddd` and `ddd`	Weekday name: Sunday through Saturday, or Sun through Sat	`=TEXT(TODAY(),"dddd")` returns a day like Monday
`h:mm AM/PM`	12-hour time	`=TEXT(NOW(),"h:mm AM/PM")` returns a time like 1:29 PM

Date codes mix M for months, D for days, and Y for years, and the codes are not case sensitive: mm/dd/yy and MM/DD/YY render the same. The thousands separator appears whenever the code has a comma enclosed by number signs or zeros, as in #,###.

How do I convert a date to text?

The reason dates dominate real TEXT usage is concatenation. The ampersand joins the value a cell stores, not the formatted display you see, and Excel drops the cell’s number formatting in the join. For a date, the stored value is one of Excel’s sequential serial numbers: January 1, 2008 is stored as 39448. Why that is, and what it makes possible, is the subject of Excel date formats; here it is enough that the raw join produces a number nobody can read.

="Due "&B2

The ampersand picks up the value B2 stores. With a due date of 15 Aug 2026 in B2, the label reads Due 46249: the serial number, not the date.

Wrapping the date in TEXT fixes the label, because TEXT renders the serial number with the format code before the join happens:

="Due "&TEXT(B2,"dd mmm yyyy")

	A	B	C	D
1	Invoice	Due date		Reminder
2	INV-2041	15 Aug 2026		Due 15 Aug 2026

TEXT renders the date in B2 with the dd mmm yyyy code, and the ampersand joins it to the label: D2 shows the text Due 15 Aug 2026.

Microsoft’s version of the same pattern is =A2&" "&TEXT(B2,"mm/dd/yy"), a text cell joined to a formatted date. Its page introduces the whole scenario by noting that TEXT seldom stands alone; it usually sits inside a larger formula, and this is the usual one.

Month names and weekday names from a date

The same date codes answer a pair of everyday questions with no lookup table and no helper column. For the 15 Aug 2026 date above:

=TEXT(B2,"mmmm")

returns the month name August, and =TEXT(B2,"mmm") the short form Aug. Swap in dddd for the weekday name, Saturday here, or ddd for Sat. Microsoft’s own example is =TEXT(TODAY(),"DDDD"), today’s day of the week, like Monday; the codes are not case sensitive. As always the results are text: right for report headings and axis labels, wrong for anything that calculates.

Leading zeros: putting them back with TEXT

Excel reads digit entries as numbers, and a number does not keep leading zeros: type 00501 into a General cell and the cell stores 501. When the zeros are already gone, TEXT can restore them for display:

=TEXT(A2,"00000")

Pads the number in A2 to five characters: 501 becomes the text 00501. The count of zeros in the code sets the total width.

Microsoft’s guidance adds that the number of zeros in the code is the total number of characters you want, so "00000" fits US zip codes and "0000000" a seven-digit part number. Excel also ships special formats for exactly these cases (zip code, zip plus 4, phone number, and social security number), and they vary by locale.

Note what this route produces: a numeric cell plus a text label made from it. If the codes should simply be text from the moment of entry, formatted as Text before anything is typed or imported, that choice and its trade-offs are covered in the leading-zeros section of numbers stored as text in Excel.

TEXT or Format Cells?

Formatting a cell does not require a formula at all. Microsoft’s TEXT page itself points at the alternative: press Ctrl+1 (Cmd+1 on the Mac) and pick a format from the Format Cells > Number dialog. The difference is what you end up holding. Format Cells changes only the display: a cell showing 40% can still store the number 0.4, and 0.4 is what every formula referencing it sees. TEXT manufactures a new value that is text.

The decision rule follows from that. If the value stays in calculations, format the cell and leave the number alone. Reach for TEXT only when you need the formatted digits inside a text string, which in practice means concatenation, labels, and exports.

Why is my TEXT formula not working?

The format code is not in quotes

The second argument is a text string, so =TEXT(B2, dd mmm yyyy) fails where =TEXT(B2, "dd mmm yyyy") works. Per Microsoft, an unquoted format code gets you an error message instead of a result.

A total went quiet

A column where TEXT output replaced the numbers sums to 0; a column where a few labels crept in among real numbers sums short. Neither raises an error, because SUM ignores text. Keep originals and labels in separate columns, or convert the text back with =D4*1 or =--D4. The same silent shrinkage from accidentally text-typed numbers is the whole subject of numbers stored as text in Excel.

Minutes came out as months

In format codes, m and mm mean months. The same letters mean minutes when they sit immediately after an h or hh code or immediately before ss: "h:mm" renders hours and minutes, while a lone "mm" renders the month.

The output looks different on another machine

Format codes lean on regional settings in documented places: the thousands separator is a comma in the US but can be a period in other locales, the built-in special formats differ by locale, and a date typed with no explicit format displays according to the machine’s regional date and time settings. When a workbook crosses borders, check the labels it builds, not just the numbers.

The short version

TEXT’s job is display: format a number and hand back text. =TEXT(1234.567,"$#,##0.00") returns the text $1,234.57.
The output is text and stops calculating: SUM ignores it and it sits left aligned. Keep the original value in its own cell and point formulas there; convert back with =D4*1 or =--D4 if needed.
The workhorse move is dates in labels: ="Due "&B2 shows a serial number like 46249, ="Due "&TEXT(B2,"dd mmm yyyy") shows the date. Month and weekday names come from mmmm and dddd.
Leading zeros: =TEXT(A2,"00000") pads for display; codes that should be text from the start are the numbers stored as text page’s territory.
Format Cells changes display and keeps the number; TEXT creates text. Prefer Format Cells for anything that still calculates. And when a workbook’s labels or totals moved between two versions, compare the two files: a cell that changed from a number to text shows up as its own finding, so a formatting decision made in one version cannot masquerade as a data change in the next.