Concatenate Question

[MSchiltz]

On tab 2 of a spreadsheet, I am trying to concatenate several cells on tab one! Everything comes together in the cell on tab 2, however it does not hold the date format. It turns it into general text so just random numbers that do not even tie to the date.

Any way to lock the date format for this idea?


Thanks!

 

[dandungan]

I'm curious how you are concatenating the data now--with a formula or vba?

 

[CodeNinja]

Have you tried something like:

= A1&TEXT(B1,"MM/DD/YYY")

You can, of course, use any format you would like...

 

[Jonmo1]

Welcome to the board..

First, FYI those random numbers do have meaning.
Dates are just serial numbers incrimenting by 1 since Jan 1 1900
Jan1 1900 = 1
Jan2 1900 = 2
etc
Apr 11 2013 = 41375

anyway..
To get your concatenate formula to format that serial # as a date, you need to include the TEXT function.

=CONCATENATE("this is the date ", TEXT(A1,"mm/dd/yyyy"))

Hope that helps.

 

[MSchiltz]

My current formula is ='THER Template'!B3&" "&'THER Template'!C3&" "&'THER Template'!D3&" "&'THER Template'!E3

Everything is pulling from tab 1... the cell that I would like to be converted to date format is the E3 at the end.

Thanks everyone for the advice! I am a newbie with Excel and like trying to speed up procedures at work! I have the ideas in mind but sometimes struggle with the coding.

Mike

 

[Jonmo1]

Try
='THER Template'!B3&" "&'THER Template'!C3&" "&'THER Template'!D3&" "&TEXT('THER Template'!E3,"mm/dd/yyyy")

adjust the mm/dd/yyyy to whatever date format you want.

 

[MSchiltz]

Wow Jonmo! You answered some of my questions I have with formulas... I was trying to manipulate that last cell and was trying to do it after the E3 and before. Makes sense...

Thanks!

 

[Jonmo1]

Great, glad to help.