outcome of CONCATENATE function

**clareleeder** · 03-20-2013, 09:00 AM

I have used the CONCATENATE function to link together a few cells. The first is a number, the second text and the last 3 are dates.

I am having a problem with the dates.

If in the original cell the date is written for example as 12/02/12 then the formulated cell is showing as a date.
If the original cell is written as 12/02/2012 the formulated cell is returning a number instead of a date

The formula = =CONCATENATE(B6,", ",M6,", ",P6,", ",O6,", ",S6)
The returned value = 51330, Pro MK 3, 20/10/11, 15/02/12, 41136

I have tried changing the original cell format to dd/mm/yy but this makes no difference.

Can you give me some other options to try.

Thank you

**Jonmo1** · 03-20-2013, 09:04 AM

Looks like P6 and O6 are not really dates, but just text strings that look like dates.
But S6 really is an excel date, and the concatenate function is returning the Serial Date value of that date.

Excel stores dates as serial numbers incrimenting by 1 from Jan 1st 1900
1 = Jan 1 1900
2 = Jan 2 1900
etc
41136 = Aug 15 2012
41353 = March 20 2013

to resolve, use the TEXT function on the REAL Date S6 to convert it to a text string

=CONCATENATE(B6,", ",M6,", ",P6,", ",O6,", ",TEXT(S6,"dd/mm/yy"))

**Kevin UK** · 03-20-2013, 09:12 AM

Hi clareleeder

Try the following:
=CONCATENATE(B6,", ",M6,", ",P6,", ",O6,", ",TEXT(S6,"dd/mm/yyyy"))

Or:
=B6&", "&M6&", "&P6&", "&O6&", "&TEXT(S6,"dd/mm/yyy")