How to Concatenate Cells and keeping Date Format in Excel

This post will guide you how to concatenate cells and keeping data format in Excel. How do I concatenate cells and retain the certain Date format with a formula in Excel 2010/2013/2016.

Concatenate Cells and Keeping Date Format

Assuming that you have a list of data in range A1:B4, which contain product names and dates. And you want to concatenate product name and date value into one cell and keeping a certain date format. How to do it. You can use a formula based on the CONCATENATE function and the TEXT function to achieve the result.  Like this:

=CONCATENATE(A1,” “,TEXT(B1,”m/dd/yyyy”))

Type this formula into a blank cell and press Enter key. And then drag the AutoFill handle over to other cells to apply this formula.

concatenate cells and keep date format1


The “Format Code” can be used in the excel Text function are shown in the below table.

Format Code Description Examples
0 only display digits in its place

#.00 – Forces the function to display two decimal places


result: $34.23

# Display the placeholder =Text(4.527,”#.##)

result: 4.53

. the position of Decimal Point =Text(342.2,”0.00″)

result: 342.20

d Day of the month or day of week

d- one or two digit number (1-31)

dd- two digit number (01-31)

ddd-abbreviated day of week (Mon to Sun)

dddd-full name of day of week(Monday to Sunnday)


result: Monday

m The Month of the Year

m- one or two digit number

mm-two digit number

mmm-abbreviated month(Jan to Dec)

mmmm-full name of month(January to December))



y year

yy-two digit representation of year(e.g.01,17)

yyyy-four digit representation of year(e.g. 2001,2017)



h Hour

h-one or two digit number (e.g. 1,23)

hh-two digit number (e.g. 01,23)


result: 14:16

m Minute

m-one or two digit representation (e.g. 1,59)

mm-two digit representation (e.g. 01,59)


result: 14:16

s Second

s-one or two digit representation (e.g. 1,59)

ss=two digit representation (e.g. 01,59)


Related Functions

    • Excel Text function
      The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…