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.
Note:
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 
=Text(34.234,”$##.00″)
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 (131) dd two digit number (0131) dddabbreviated day of week (Mon to Sun) ddddfull name of day of week(Monday to Sunnday) 
=Text(TODAY(),”DDDD”)
result: Monday 
m  The Month of the Year
m one or two digit number mmtwo digit number mmmabbreviated month(Jan to Dec) mmmmfull name of month(January to December)) 
=Text(TODAY(),”MM/DD/YY”)
result:11/06/17 
y  year yytwo digit representation of year(e.g.01,17) yyyyfour digit representation of year(e.g. 2001,2017) 
=Text(TODAY(),”MM/DD/YY”)
result:11/06/17 
h  Hour
hone or two digit number (e.g. 1,23) hhtwo digit number (e.g. 01,23) 
=Text(14:16,”hh:mm”)
result: 14:16 
m  Minute
mone or two digit representation (e.g. 1,59) mmtwo digit representation (e.g. 01,59) 
=Text(14:16,”hh:mm”)
result: 14:16 
s  Second
sone 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 buildin 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)…

Excel Text function