How to Concatenate Dates in Excel: 6 Easy Ways

Do you want to concatenate multiple dates or the date with other data formats and place them in a single cell? Learn how to concatenate dates in Excel for both personal and professional purposes.

Besides storing data in an array of formats, Excel allows you to perform various calculations and formatting using this software.  Among these, date is one of the common formats used for data storage in Excel.

Sometimes, you may need to put multiple dates in a cell or merge dates with other data types, like text or time. For that, you need to perform the date concatenation in Excel.

Read this article to know how to concatenate dates in Excel. Considerating the different expertise levels of the readers, I’ve discussed the process using multiple methods.

Reasons to Concatenate Dates in Excel

Through the concatenation of dates in Excel, users can work with their data with greater flexibility and have more control over the data. Here are some common reasons people want to concatenate dates in Excel.

1. With merged dates, you get a unified date format in a cell. It’s not only visually appealing but also easier to interpret during data presentation and organization.
2. Concatenated dates are also helpful for arranging the dates in the desired order. Such data can be sorted and filtered for data analysis and data comparison.
3. People also use it to create custom labels and identifiers for their data, like creating a unique ID combining dates with initials.
4. Concatenated dates are also used in dynamic and comprehensive reports. By merging dates with project status or sales targets, you can provide insights into the performance of a particular time or date range.
5. When Excel users need to perform date-based calculations to find out duration, time interval, or age difference, merged dates make the process highly convenient.
6. Concatenating dates is also useful during Excel integration with other applications or systems. It offers a standard date format to ensure compatibility and hassle-free data transfer.

Concatenating Dates in Excel Using the CONCATENATE Function

CONCATENATE or CONCAT is the simplest way to join two or more strings with dates or two dates. Suppose there are two columns on your spreadsheet: Name and DoB. You need to join the texts under the Name column with the dates under the DoB column. Find below the steps as well as the formula.

1. Highlight the cell where you want to concatenate a text and a date.
2. Double-click the cell and enter the following formula:

=CONCAT(A2,TEXT(B2,"mm/dd/yyyy"))

3. Press Enter to combine two values in the target cell.
4. Drag the fill handle down the column to concatenate the rest of the texts and dates.

Are you on a dated Excel desktop app (Excel 2010, Excel 2007, etc.)? Use the following formula instead:

=CONCATENATE(A2,TEXT(B2,"mm/dd/yyyy"))

Now, you can’t use the resultant data as is. Because the values are outcomes of a formula. So, you must convert these into text values. Here’s how:

1. Select the entire data that you want to convert to text.
2. Press Ctrl + C to copy the values.
3. Go to the column where you want the text values of the copied data.
4. Press Ctrl + Alt + V to bring up the Paste Special dialog box.
5. There, select Values and click OK.

Now you got text strings of the concatenated text and dates in your Excel worksheet.

Use Ampersand to Concatenate Dates in Excel

Another quick way to join a text and a date or two dates is by using the Ampersand symbol in Excel. Here, you can also add a delimiter or a default text in between texts and dates or two dates when using the Ampersand symbol. Here’s how this method works:

1. Select an empty cell on your worksheet.
2. Enter the following formula into the cell and hit Enter:

=A2&"-"&TEXT(B2,"mm/dd/yyyy")

3. Don’t forget to replace the cell references according to your own dataset.

Note: The "-" formula element work as the delimiter between the text and date. You can change it if you want to. For example, you can have a space between two dates or a text and a date by replacing the above with " ". So, the delimiter goes inside the quotes.

4. You should see the joined value in the selected cell.
5. Use the fill handle to apply the formula to the rest of the Name and DoJ data.

Let’s consider you want to join a text and a date along with a default text and create a sentence. For example, you got the Name and DoJ columns on your dataset. You want to create a sentence that reads “Alisha Joined Acme IT on 05/20/2001” automatically for all the names and dates. Here’s how you can do this:

1. Select an adjacent cell to the existing columns of data.
2. Copy and paste the following formula inside the cell and hit Enter:

=A2&" Joined Acme IT on "&TEXT(B2,"mm/dd/yyyy")

3. You get the desired sentence in the highlighted cell.
4. Drag the fill handle down the column to create the sentence for the rest of the Name and DoJ data.

Want to insert any other text in the above formula? Just replace the text in this formula element without changing the quotes and spaces. For example, the following formula will produce this sentence, “Alisha left Acme IT on 05/20/2001.”

=A2&" left Acme IT on "&TEXT(B2,"mm/dd/yyyy")

To use the above sentences or joined names and dates as texts, you must convert the formula outputs to values by following the Paste Special methods mentioned earlier in this article.

Utilize TEXTJOIN Function for Concatenating Dates in Excel

TEXTJOIN is yet another function that lets you combine two or more dates without losing the date format. Here, you select a delimiter at the beginning and choose a TRUE or FALSE value for empty cells. Then, you enter the date references wrapped within a TEXT function. Here’s a real-world example:

Also read: How To Convert True/False To Yes/No In Excel

You got columns for Start Date, End Date, and Levels of education for several job applicants. You need to show the educational qualifications and tenure of those candidates in one cell, preferably in a phrase. Here’s how:

1. Highlight the target cell and enter the following formula:

=(TEXTJOIN(" to ",TRUE,TEXT(A2:B2,"mm/dd/yyyy")))& C2

2. You need to customize the formula according to your own dataset. Below are the references:
   • A2:B2 are the start and end dates
   • C2 supplies the level of education in parenthesis
3. Hit Enter and you get a phrase that explains the educational qualification and tenure of the applicant.

4. Drag the fill handle down the column to automatically get the rest of the values.

The resulting values need conversion to texts using the Paste Special command as explained previously in this article.

Use a Quick VBA Script

Do you need to concatenate thousands of rows of dates or dates and texts in another column of your choosing? You can use this VBA script that combines two dates or dates and texts in a flash. Here are the VBA code and steps:

1. Bring up the Excel VBA Editor on your worksheet by hitting Alt + F11 keys on the keyboard.
2. Click Insert and choose Module.
3. In the blank module, copy and paste the following VBA script:

Sub ConcatenateDates()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim startDates As Range
    Dim endDates As Range
    Dim concatenatedDates As Range
    Dim i As Long
    
    ' Set the worksheet object
    Set ws = ThisWorkbook.Worksheets("Sheet3") ' Replace "Sheet3" with your sheet name
    
    ' Find the last row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Set the ranges for start dates, end dates, and concatenated dates
    Set startDates = ws.Range("A2:A" & lastRow)
    Set endDates = ws.Range("B2:B" & lastRow)
    Set concatenatedDates = ws.Range("C2:C" & lastRow)
    
    ' Loop through each row and concatenate the dates
    For i = 1 To lastRow - 1
        concatenatedDates(i, 1).Value = Format(startDates(i, 1).Value, "yyyy-mm-dd") & " To " & Format(endDates(i, 1).Value, "yyyy-mm-dd")
    Next i
End Sub

4. You need to customize the above code according to your own dataset. Here are the code references:
   • Change Sheet3 to the current sheet name of your Excel worksheet or where you want to perform the concatenation.
   • Change column A to another column that has the start date values.
   • Modify the reference "A" to the above column’s header like "B", "C", etc.
   • "A2:A" must be changed according to the actual reference for the start dates.
   • "B2:B" also needs changing according to the end dates in your dataset.
   • "C2:C" is the reference for the destination of the concatenated values. So, change accordingly.
   • "yyyy-mm-dd" is the display format for the dates. You can change it to "dd-mm-yyyy", "mm-dd-yyyy", and so on.
   • " To " is the delimiter. Change it to something else but don’t forget to include the quotes.
5. Click the Save button and close the VBA Editor.

6. Now, hit Alt + F8 to bring up the Macro window.
7. There, select the ConcatenateDates macro and hit the Run button.
8. You should find the joined values in column C or the one you selected.

Concatenate Dates in Excel Using Power Query

Are you importing massive data consisting of dates and text strings using Power Query? You can concatenate dates inside the Power Query tool and then import the organized data into a new Excel worksheet. Here are the steps you should try:

1. Click the Data tab on the Excel ribbon and select Get Data inside the Get & Transform Data commands block.
2. In the context menu, choose the preferred data source. For example: From Database > From Microsoft Access Database.
3. You should now see your data in the Power Query Editor tool.

4. Press the Ctrl key and click the columns for which dates and texts need to be joined.
5. Right-click and choose Merge Column from the context menu.

6. On the Merge Columns dialog box, choose a Separator and a New column name.
7. Click OK and the values of two or more columns will be merged into one.
8. Click File and then select Close & Load to insert the data in your worksheet in a new sheet tab.

How to Use Office Scripts to Concatenate Dates

Find below the Office Scripts code and steps to automate the concatenation process in Excel for Microsoft 365 and Excel for the web app. You must possess Microsoft 365 Business Standard or a better subscription to use this feature.

1. Click the Automate tab on the Excel ribbon and select New Script.

2. Inside the Code Editor panel, copy and paste the following Office Scripts code:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Set range C2 on selectedSheet
    selectedSheet.getRange("C2").setFormulaLocal("=CONCAT(A2,TEXT(B2,\"mm/dd/yyyy\"))");
    // Paste to range C3:C6 on selectedSheet from range C2 on selectedSheet
    selectedSheet.getRange("C3:C6").copyFrom(selectedSheet.getRange("C2"), ExcelScript.RangeCopyType.all, false, false);
}

3. The above code has the following data inputs:
   • A2: name text
   • B2: date
   • C2: destination of concatenation
4. Click the Save script button.
5. Now, click the Run button.
6. Excel will perform the concatenation automatically for the selected cell ranges.
7. Results will show up under column C.

Ensure you’ve modified the above code according to your own dataset. If you’ve got the same data organization as shown in this tutorial, then only change the code element C3:C6 to the desired range like C3:C100.

Conclusions

As an Excel user, you might want to know how to concatenate dates in Excel due to various reasons. You might need it for an office project, a college presentation, or any personal use. 

Regardless of the reason, you can easily concatenate dates in Excel using the above-mentioned methods. Since different users have different expertise levels in Excel, the article presents several approaches that you can use to concatenate dates and texts or two dates in Excel. 

Which method did you find to be the easiest? Tell us in the comment section. You can also read how to insert every other row in Excel.