date concatenation confusion

[spence]

I have three columns: FirstName, LastName, and Date.
I want to create a fourth column that looks like this:

LastName, FirstNameate

Assuming FirstName is in column A, LastName is in column
B, and Date is in column C, I am using the following
formula in column D for my concatenation:

=B1&","&" "&A1&":"&C1

In column D where the date should be I'm getting a five
digit number whose source baffles me. (For instance, where
I should have Smith, Andi:5/1/2003 I'm instead getting
Smith, Andi:38107) Can someone tell me where this five
digit number is coming from and how to make it show up
properly as the date?

Thanks,
Spence

 

[Frank Kabel]

Hi
try
=B1&","&" "&A1&":"&TEXT(C1,"DD/MM/YYYY")

 

[Ron de Bruin]

Hi Spence

You can use the Text function

Syntax

TEXT(value,format_text)

 

[Jason Morin]

That number you are seeing is the actual date as XL sees
it. It's just not in the format mm/dd/yy. I recommend you
read up on date/time serials in XL:

http://www.cpearson.com/excel/datetime.htm#AddingTimes

and then use this formula:

=B1&","&" "&A1&":"&TEXT(C1,"mm/dd/yy")

HTH
Jason
Atlanta, GA

 

[peter]

Why not use the concatenate formula itself

In other words, write in that fourth column the following

=concatenate(b1," ",a1," ",c1)? where the " " adds a blank between each entry?