preserving format in a formula

[exceldoofus]

I'm trying to concatenate two columns in a spreadsheet that contain dat
information. I want the data concatenated into a new column with th
dates separated by a ~. I found a formula that does the concatenatio
correctly, BUT, it converts my dates to the numerical value associate
with the dates.

My husband suggested putting in an apostrophe before the dates in th
originating cell. Sure enough, when I tried that, the formulated colum
converted to the value I wanted. We then wrote a macro that we though
would allow me to quickly convert all the 10k rows in my spreadsheet
But, the macro wants to preserve the date info for the cell from whic
it was recorded. So, we had a macro that was F2 (edit); Home (took m
to the front of the cell); ‘ (add the apostrophe); <enter> We though
it was awesome until we realized each time we ran the macro.. though i
was turning the formulated cell into a date, it was the date from th
macro, not the correct one.

Does anyone know how to either a) preserve the date format from th
gitgo or b) create a macro that is intuitive enough to work off of th
contents of each cell. FYI, I've tried paste special...I've trie
tweaking the cell format to text etc..but to no avail.

Here's an example of how the data looks and what my formula is:

Column 1 Column 2 Formul
Column (=A2&"~ "&B2)
01/13/2000 02/29/2000 36538~36585

Any help would be HUGELY appreciated. If I don't solve this I may hav
to hand concatenate each and every cell -- all 10k of them.

Thank you

 

[Alok]

Try this method of concatenation(assuming your dates are in A1 and B1)

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

Alok

 

[Ron Rosenfeld]

I'm trying to concatenate two columns in a spreadsheet that contain date
information. I want the data concatenated into a new column with the
dates separated by a ~. I found a formula that does the concatenation
correctly, BUT, it converts my dates to the numerical value associated
with the dates.

My husband suggested putting in an apostrophe before the dates in the
originating cell. Sure enough, when I tried that, the formulated column
converted to the value I wanted. We then wrote a macro that we thought
would allow me to quickly convert all the 10k rows in my spreadsheet.
But, the macro wants to preserve the date info for the cell from which
it was recorded. So, we had a macro that was F2 (edit); Home (took me
to the front of the cell); ‘ (add the apostrophe); <enter> We thought
it was awesome until we realized each time we ran the macro.. though it
was turning the formulated cell into a date, it was the date from the
macro, not the correct one.

Does anyone know how to either a) preserve the date format from the
gitgo or b) create a macro that is intuitive enough to work off of the
contents of each cell. FYI, I've tried paste special...I've tried
tweaking the cell format to text etc..but to no avail.

Here's an example of how the data looks and what my formula is:

Column 1 Column 2 Formula
Column (=A2&"~ "&B2)
01/13/2000 02/29/2000 36538~36585

Any help would be HUGELY appreciated. If I don't solve this I may have
to hand concatenate each and every cell -- all 10k of them.

Thank you.

I believe the formula you want to use is:

=TEXT(A2,"mm/dd/yyyy") & " - " & TEXT(B2,"mm/dd/yyyy")

Copy/drag down as far as necessary.




--ron

 

[exceldoofus]

Oh my gosh. Thank you so much. That worked!!