Excel Concatenate with Date: Formatting & Formulas

Formulas & functions · Updated November 22, 2026

To concatenate a date in Excel while keeping it readable, use the TEXT function inside your concatenation formula, like =A2&" "&TEXT(B2,"mm/dd/yyyy"). Without TEXT, Excel displays the date as a raw serial number (e.g., 45292) because dates are stored as numbers. This article explains why that happens, how to format dates in concatenation, and common pitfalls.

Why Dates Turn into Numbers When Concatenating

In Excel, a date is actually a serial number that represents the number of days since January 0, 1900 (for Windows). For example, July 4, 2024, is stored as 45477. When you use the & operator or the CONCATENATE function, Excel converts the date to its underlying numeric value unless you explicitly format it. This is a common source of confusion because the cell may display a formatted date, but the underlying value is still a number.

To avoid this, you must convert the date to text using the TEXT function, which lets you specify the exact date format you want in the resulting string.

Basic Concatenation with TEXT Function

The TEXT function syntax is TEXT(value, format_text). For dates, you can use any valid date format code. Here are common examples:

  • =A2 & " " & TEXT(B2, "mm/dd/yyyy") → 07/04/2024
  • =A2 & " " & TEXT(B2, "mmmm d, yyyy") → July 4, 2024
  • =A2 & " " & TEXT(B2, "dd-mmm-yy") → 04-Jul-24

Assume cell A2 contains “Event date:” and B2 contains the date 7/4/2024. The formula =A2 & " " & TEXT(B2, "mmmm d, yyyy") returns “Event date: July 4, 2024”.

You can also use the CONCATENATE function, but it offers no advantage over &; the & operator is shorter and more flexible. For example: =CONCATENATE(A2, " ", TEXT(B2, "mm/dd/yyyy")).

Using TEXTJOIN for Ranges or Delimiters

If you need to concatenate a range of cells with a delimiter, use TEXTJOIN (available in Excel 2019 and later). For instance, with names in A2:A5 and dates in B2:B5, you can combine each pair: =TEXTJOIN(", ", TRUE, A2:A5 & " " & TEXT(B2:B5, "mm/dd/yyyy")). This formula concatenates all pairs, separated by a comma and space. The TRUE argument ignores empty cells. Note that this is an array formula; in older Excel versions you might need to press Ctrl+Shift+Enter, but in Excel 365 it works normally.

How to Choose the Right Date Format

The format code you choose depends on your audience and regional settings. Common formats include:

  • "yyyy-mm-dd" – ISO standard, good for sorting or international use.
  • "mm/dd/yyyy" – US style.
  • "dd/mm/yyyy" – UK/Australian style.
  • "mmmm d, yyyy" – full month name for readability.
  • "ddd, mmm d" – abbreviated day and month, e.g., Thu, Jul 4.

You can also include literal text in the format, such as "mmm d, yyyy" or even "dddd, mmmm d, yyyy" for the full weekday. For example, =TEXT(B2, "dddd, mmmm d, yyyy") returns “Thursday, July 4, 2024”.

Common Problems and How to Fix Them

Here are frequent issues when concatenating dates:

  • Date shows as number: You forgot to wrap the date in TEXT. Solution: use TEXT as shown.
  • Wrong date format: Your format code doesn’t match your regional date settings. For example, "mm/dd/yyyy" might display as month/day/year, but if your system uses dd/mm, it may interpret differently. Always test.
  • Leading zeros lost: If you want a two-digit month or day, use mm or dd instead of m or d.
  • Time included: If your cell contains a date and time, use a format like "mm/dd/yyyy hh:mm" to include both.
  • Concatenating with a formula that returns a date: If the date comes from a formula like TODAY(), wrap it in TEXT as well: =TEXT(TODAY(), "mm/dd/yyyy").

Alternatives to TEXT: Custom Number Formatting

If you only need to display a concatenated string without storing it as text, you can use a custom number format. Select the cell, press Ctrl+1 (or Cmd+1 on Mac), go to Number > Custom, and enter a format like "Event: "mm/dd/yyyy. However, this only changes the display; the cell still contains the date serial number. If you need the result as a text string (for email, file names, or further text processing), you must use TEXT.

Frequently Asked Questions

Can I concatenate a date without using TEXT? Yes, but the result will show the serial number. To get a formatted date, you must use TEXT or another method like DAY, MONTH, YEAR functions to extract parts and combine them manually.

How do I concatenate a date and keep the leading zero for month/day? Use two-digit codes: mm for month, dd for day. For example, =TEXT(B2, "mm/dd/yyyy") yields 07/04/2024, not 7/4/2024.

Why does my concatenated date show as #####? This usually means the column is too narrow to display the full text. Widen the column or wrap text. It can also happen if the format code is invalid.

What’s the difference between CONCATENATE and & in Excel? Functionally, none for simple concatenation. & is shorter and easier to read. CONCATENATE is kept for compatibility with older versions.

Can I concatenate a date range like “July 4-6”? Yes, use TEXT for each date: =TEXT(B2, "mmmm d") & "-" & TEXT(C2, "d") where B2 is start and C2 is end. For example, July 4-6.

Does this work in Google Sheets? Yes, the TEXT function exists in Google Sheets with the same syntax. The & operator also works. The main difference is that Google Sheets may use different date format codes for regional settings, but mm/dd/yyyy generally works.

Concatenating Dates in Excel for the Web and Mac

The steps are identical in Excel for the web and Excel for Mac. The TEXT function works the same, and the & operator is universal. On Mac, the keyboard shortcut for Format Cells is Cmd+1, and the formula bar works the same. If you use Excel for the web, you can use the same formulas; just ensure you have the latest version for TEXTJOIN.

Remember that dates are stored as numbers, so any formula that concatenates without TEXT will produce a numeric string. Always use TEXT to control the output format.

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.