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

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 (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)

=Text(TODAY(),”DDDD”)

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))

=Text(TODAY(),”MM/DD/YY”)

result:11/06/17

y year

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

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

=Text(TODAY(),”MM/DD/YY”)

result:11/06/17

h Hour

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

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

=Text(14:16,”hh:mm”)

result: 14:16

m Minute

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

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

=Text(14:16,”hh:mm”)

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)…

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar