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: useTEXTas 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
mmorddinstead ofmord. - 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 inTEXTas 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
- Snowflake SPLIT: How to Split Strings by Delimiter
- Snowflake SQL SPLIT: Syntax, Examples, and Alternatives
- Snowflake LATERAL SPLIT_TO_TABLE: Syntax and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Java String Predefined Functions: Methods and ExamplesJava’s predefined string operations are methods of the String class, not standalone functions. Common examples include length(), charAt(),…
- Align Right in Excel: Shortcut, Menu, and Mac KeysIn Excel for Microsoft 365 on Windows, select the cell or range and press Alt, H, A, R—one…
- Excel Date Calculation Methods and FormulasIn Excel, calculate dates by adding or subtracting dates and day counts, or use functions such as DATE,…
- Arduino Functions: How to Write and Use Them in a SketchTo write a function in Arduino, give it a return type and name, add any input parameters in…
- Text Filter in Excel With Multiple Criteria ExplainedTo filter text in Excel by multiple criteria, select your data, choose Data > Filter, open a column’s…
- COUNTIF Excel Text in Cell: Exact or Partial MatchesTo count cells that contain particular text anywhere in their contents, use =COUNTIF(A2:A100,"*apple*"); the asterisks allow any characters…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.