Function Problems

How to Combine or Concatenate Dates from Multiple Excel Cells

Natalie Taylor Oct 9, 2026 871 views

Question details

The user needs to display dates from multiple noncontiguous cells (like A1, B3, and R14) into a single cell, either by concatenating them or extracting the latest date.

How to Combine or Concatenate Dates from Multiple Excel Cells

Product: Excel
Device & OS: not provided
Scenario: Consolidating scattered date data or formula outputs from different spreadsheet cells into a single, readable cell for easier reporting.
Observed behavior: When users try to combine dates using standard operators, Excel converts the dates into unformatted serial numbers instead of displaying readable date strings.

Before you start

Ensure that the source cells contain valid Excel date values or formulas that evaluate to standard dates, as text strings masquerading as dates may not respond correctly to date-specific formatting functions.

Solution 1Recommended

Concatenate Multiple Dates Using the TEXT Function

Use the TEXT function combined with the ampersand (&) operator to join multiple dates into one cell without losing their specific date formatting.

Because Excel stores dates as numeric serial numbers, simply combining them will result in a string of numbers (e.g., 44200, 44201). The TEXT function converts these numerical values back into readable date formats before joining them together.

Select the destination cell

Click on the empty cell where you want the combined dates to be displayed.

Input the TEXT formula

Type the formula: =TEXT(A1,"mm/dd/yyyy") & ", " & TEXT(B3,"mm/dd/yyyy") & ", " & TEXT(R14,"mm/dd/yyyy") substituting the cell references with your actual target cells.

Execute the formula

Press the Enter key. The cell will now display the combined dates separated by commas.

Concatenate Multiple Dates Using the TEXT Function

Customizing the Date Format: You can change the "mm/dd/yyyy" parameter to match your regional preferences, such as "dd-mm-yyyy" or "dd-mmm-yyyy" (which displays as 01-Jan-2023).

Solution 2

Extract the Latest Date Using the MAX Function

If you only need to display the most recent date from a group of scattered cells rather than listing them all, the MAX function is the most efficient method.

Effortless Spreadsheet Management

Easily Manage and Combine Dates with WPS Spreadsheet

WPS Spreadsheet fully supports advanced Excel functions like TEXT, MAX, and CONCATENATE, making it incredibly easy to combine dates or perform complex data consolidations without compatibility issues.

1. Open your document: Launch WPS Spreadsheet and open the file containing your scattered dates.
2. Select the target cell: Click on the specific cell where you want the combined dates to appear.
3. Type the formula: Enter the formula =TEXT(A1,"mm/dd/yyyy") & ", " & TEXT(B3,"mm/dd/yyyy").
4. Apply the calculation: Press Enter to execute the formula and instantly view your concatenated dates.

Fully compatible with Microsoft Excel formulas, date formats, and serial numbersIntuitive formula bar with helpful function syntax suggestionsFree, lightweight, and fast alternative for daily spreadsheet managementSupports advanced data formatting and text joining tools

microsoft office alternative - wps office

Frequently Asked Questions

Why do my dates turn into 5-digit numbers when I concatenate them?

Excel stores dates internally as serial numbers (e.g., 44200 represents Jan 1, 2021). When you use the ampersand (&) or CONCATENATE function without formatting, Excel strips the date format and reveals the underlying serial number. Using the TEXT function resolves this.

Can I combine dates from a contiguous range like A1:A10?

Yes. If you are using a newer version of Excel or WPS Spreadsheet, you can use the TEXTJOIN function combined with the TEXT function. The formula would look like this: =TEXTJOIN(", ", TRUE, TEXT(A1:A10, "mm/dd/yyyy")).

Does the TEXT function work if my source cells contain formulas instead of manual dates?

Yes. As long as the formula in the source cell successfully evaluates to a valid date or a date serial number, the TEXT function will recognize it and format the output correctly.

How do I add descriptive text before the concatenated dates?

You can add text by enclosing it in quotation marks and linking it with an ampersand. For example: ="Project Deadlines: " & TEXT(A1,"mm/dd/yyyy") & " and " & TEXT(B3,"mm/dd/yyyy").

Excel & Data Problems