Excel Date Converts to Serial Date during CONCATENATE 3

[ljsmith91]

I have a cell formatted in date format as mm/dd/yy. I have another cell that is number format and a 3rd cell in text format that has "Total Storage".

I want to concatenate these 3 cells into one cell using the CONCATENATE function. When I do this, the date cell switches or converts to SERIAL date instead of the mm/dd/yy date the initial cell is formatted in.

Here is the coded cell function:
=CONCATENATE(occupancy!C1," for ", A2," = ",C2)

occupancy is the spreadsheet
C1 has date "06/05/03"
A2 has "Total Storage"
C2 has 44371

The function outputs to the cell as "Total Storage for 37797 = 44371"

37797 is the serial date instead of the mm/dd/yy date. How do I get it to keep the mm/dd/yy format after the concatenate ?

Thanks for any help.

 

[xlbo]

You have to convert the date to text as CONCATENATE creates text & BTW - you don't need the concatenate function - just use &:

=text(occupancy!C1,"dd/mm/yy&quot & " for " & A2 & " = " & C2

should do the trick

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[ljsmith91]

XLBO,

Thanks so much. I gave your exact code a try and it didnt work but it was the right path to go down as opposed to the one I was taking previously. Here is the actual code I got to work.

=TEXT(C1,"&quot & " for " & TEXT(A2,"mm/dd/yy&quot & " = " & TEXT(C2,"#&quot

It did the trick for me. You saved me much time and aggravation.

Have a great day.