Concatenate formula // Date Weird

**nicci113** · 03-15-2013, 07:39 PM

Hi Guys,

Not sure how to explain so made the attached file as well. I'm trying to use concatenate function which seems to be working fine, but the date which I am merging is coming as number instead of date in itself. I checked the format and in their it appear as number, but when i change it to date format it still appear as weird number... If you look column D it appear as date but after function they are number instead.any idea why its happening??
Sorry for not very clear, please guide me through its cruical to me
Regards,

**martindwilson** · 03-15-2013, 07:47 PM

when you concatenate or join a real date it ignores the formating and gives the date serial number(put a date in a cell and format general to see one)
so you have to make it go back to a text date TEXT(D1,"DD MMM YYYY")

=CONCATENATE(TEXT(D1,"DD MMM YYYY")," ",D2)
or =CONCATENATE(TEXT(D1,"DD MMM YYYY "),D2)
or=TEXT(D1,"DD MMM YYYY")&" "&D2
or=TEXT(D1,"DD MMM YYYY ")&D2

**Harribone** · 03-15-2013, 07:48 PM

=TEXT(D1,"dd mmmm yyyy")&" "&D2

Should do the job.

**JBeaucaire** · 03-15-2013, 07:49 PM

Dates in Excel ARE large numbers like 43567.... then Excel formats that number to display as a date format you choose.

If you try to concatenate a date cell, it will reveal the actual underlying number as it is converted to text. Concatenation returns a text string, FYI.

But now that you know that, you can use the TEXT() function to format the date 'text' in the date 'format' that you want.

=TEXT(D1, "dd mmmm yyyy ") & D2

**nicci113** · 03-15-2013, 07:55 PM

Thanks guys! I was googling to see why Excel is converting date into numeric format even though within format we have selected date function, All the above suggestion are working, but I want to know how to change the formating to make it as date and not to complicate.. more then that I want to add into my knowledge base.. I can live with text function but want to know why its happening and how to change. Again guys you are life and soul to people like me and a big THANK YOU!