TEXTJOIN Dates in Excel: Formulas and Fixes

Formulas & functions · Updated November 20, 2026

To join dates in Excel as readable text, wrap them in TEXT before combining them with TEXTJOIN. If actual Excel dates are in A2:A4, this formula returns them separated by vertical bars: =TEXTJOIN(" | ",TRUE,TEXT(A2:A4,"mmm d, yyyy")). For dates of January 5, January 8, and January 12, 2026, the result is Jan 5, 2026 | Jan 8, 2026 | Jan 12, 2026.

Why dates need special handling

Excel stores most dates as numbers, not as the words and punctuation you see on the worksheet. A cell might display as Jan 5, 2026, while its underlying value is a serial number that Excel uses for date calculations. The cell’s number format controls how that number appears in the grid; it does not turn the stored value into a text string.

That distinction matters when joining values. A formula that combines a date directly with a text value can expose its serial number instead of its familiar date display. Applying TEXT to the date converts it to text in a format you choose. TEXTJOIN then combines that formatted text with other values and a delimiter.

This is useful for creating summaries, labels, email-ready lists, report headings, or a single text field for export. The result is text, however, not a collection of dates that Excel can sort chronologically or use directly in date arithmetic. Keep the original date cells if you still need to calculate with the dates.

How the formula works

In the example formula, the first argument, " | ", is the separator inserted between dates. The second argument, TRUE, tells Excel to ignore empty cells. The remaining argument formats the range as month, day, and four-digit year. The code mmm means an abbreviated month name, d means the day number, and yyyy means a four-digit year.

TEXTJOIN takes the general form =TEXTJOIN(delimiter,ignore_empty,text1,[text2],...). You can supply cell references, text constants, ranges, or expressions that return text. In Microsoft 365, the TEXT expression in the example can format the range and pass its results to TEXTJOIN in one formula, so you do not need a helper column for each date.

Choose the delimiter for its intended use. A comma and space are familiar in prose; a line break can make a long list easier to read in one cell; and a pipe or semicolon can be more convenient when the joined text will be copied into another system. The separator is literal text, so include spaces or punctuation inside its quotation marks exactly as you want them to appear.

Choose a date format and delimiter

The format inside TEXT is independent of the date’s cell format. If a worksheet displays a date as 1/5/2026, you can still format it as January 5, 2026 in the joined text. Common choices include "m/d/yy" for a compact numeric date, "dd-mmm-yyyy" for a day-first date, and "mmmm d, yyyy" for a full month name.

For example, with dates in A2:A4, this version uses a comma and space between dates and spells out each month: =TEXTJOIN(", ",TRUE,TEXT(A2:A4,"mmmm d, yyyy")). It returns a string such as January 5, 2026, January 8, 2026, January 12, 2026. Because the date itself contains a comma, a pipe separator may be easier to scan in this particular format.

Consider the audience and what will happen to the result. Month names can avoid ambiguity between day-first and month-first numeric dates, but they make the text longer. A format such as "yyyy-mm-dd" puts the year first and is often convenient for consistent labels or data exchange, but the finished string remains text. If another application needs true date values, export the original date cells or use a structured data format rather than relying on a joined label.

Join a date with a label or other cell

You do not have to combine dates only with other dates. With a label in A2 and an actual date in B2, this formula creates a short phrase: =TEXTJOIN(" ",TRUE,A2,TEXT(B2,"mmm d, yyyy")). If A2 contains Deadline: and B2 contains January 5, 2026, the result is Deadline: Jan 5, 2026. The formula uses a space as its delimiter and formats the date before joining it.

For a simple two-part combination, the ampersand operator is another option. With a description in A2 and a date in B2, =A2&" — "&TEXT(B2,"mmm d, yyyy") produces the same kind of label, with an em dash between the pieces. The ampersand is handy for a fixed, short expression; TEXTJOIN is often clearer when combining a range or when you want Excel to skip blanks.

If the date is accompanied by a time, include a time code in the TEXT format, such as "mmm d, yyyy h:mm AM/PM". Check the intended output before sharing it: hiding the time in the source cell’s display does not remove the time value, and the format you specify determines whether it appears in the joined string.

Blanks, missing dates, and separators

The TRUE argument tells TEXTJOIN to ignore empty cells among its text arguments. This is useful when a list has gaps: Excel does not add a delimiter just for an empty cell that it ignores. For example, if A2 and A4 contain dates and A3 is blank, a formula joining A2:A4 with TRUE joins the two nonblank entries without an empty item between them.

Ignoring blanks does not mean that every kind of unwanted value disappears. A cell containing a space is not truly empty, and an error in a referenced value can still cause the formula to return an error. If a date field contains a placeholder such as a hyphen or the text N/A, that value is not blank and may be included. Clean or validate source data when such entries should not appear.

Setting the second argument to FALSE makes TEXTJOIN preserve empty text arguments, which can result in adjacent delimiters where blank items occur. That may be appropriate when each position in the output has meaning, but it often makes a readable date list look untidy. Decide whether blanks mean “omit this entry” or “leave an empty position” before selecting the option.

Common problems and how to fix them

  • A number appears instead of a date: Format the date with TEXT inside the joining formula. Changing the source cell’s display format alone may not control the text produced by a formula.
  • The output shows an unexpected date order: Specify the pattern explicitly, such as "dd-mmm-yyyy" or "mmm d, yyyy". Numeric formats like "m/d/yy" can be ambiguous to readers in different regions.
  • The formula returns an error: Check that the source cells contain valid Excel dates rather than text that merely looks like a date, and check for existing errors in the range. Text imported from another file may need conversion before date formatting works as intended.
  • There are extra separators: Confirm that the second argument is TRUE, and inspect cells that look blank for spaces or placeholder text. Those values are not empty cells from Excel’s perspective.
  • The formula is rejected when entered: Excel’s argument separator may depend on regional settings. If your installation uses semicolons between arguments, follow the separator Excel expects; the formulas shown here use commas.

Also remember that a date-like text value and a real date are different. If TEXT does not produce the expected result, verify the source by changing a test cell’s number format or checking how it behaves in a date calculation. Do not assume that visual appearance proves the data is stored as a date.

Entering and reviewing the formula

In Excel for Microsoft 365 on Windows, select the cell where the combined text should appear, type the formula, and press Enter. You can edit it in the formula bar or press F2 while the cell is selected. To inspect or change a cell’s number format, use Home > Number group > Number Format, or open Format Cells with Ctrl+1 on Windows. On Mac, Command+1 opens Format Cells; the formulas themselves work the same way in current Microsoft 365 for Mac.

Once the formula is in place, review the actual result rather than relying on the source cells’ display. Widen the output column or turn on Wrap Text if a long joined list is hard to read. If you copy the result to another workbook or application, remember that the formula output is text; copying and pasting values preserves the finished string but not the underlying date values or formula.

Frequently Asked Questions

Can I use TEXTJOIN on dates without TEXT?
It is better to format dates with TEXT first. Otherwise, the result may show the underlying date serial number rather than the date style visible in the worksheet cell.

Can TEXTJOIN put each date on a new line?
Yes. Use a line-break character as the delimiter: with dates in A2:A4, enter =TEXTJOIN(CHAR(10),TRUE,TEXT(A2:A4,"mmm d, yyyy")). Turn on Wrap Text for the result cell to display the lines. On Windows, the line break is represented by CHAR(10); it is also used for this purpose in current Excel for Mac.

Does TEXTJOIN change the original dates?
No. It returns a text result in the formula cell and leaves the source dates unchanged. The joined output is not itself a date range and cannot replace the original values for date calculations.

Why does a date look different in the joined result than in its cell?
The source cell’s number format controls its worksheet display, while the format string inside TEXT controls the joined result. Set that string to the exact order and level of detail you want.

Can I use TEXTJOIN to combine dates from separate cells?
Yes. Supply the cells or ranges as arguments and format each date with TEXT where needed. For a small number of dates, a direct formula is simple; for many dates, a contiguous range such as A2:A20 is easier to maintain.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.