# Concatenate Question

#### MSchiltz

##### New Member
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!

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I'm curious how you are concatenating the data now--with a formula or vba?

Have you tried something like:

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

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

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.

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

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.

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!

Replies
1
Views
223
Replies
1
Views
552
Replies
5
Views
491
Replies
2
Views
579
Replies
1
Views
379

1,196,263
Messages
6,014,311
Members
441,814
Latest member
youngstubbs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back