preserving format in a formula
 

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.


--
exceldoofus
------------------------------------------------------------------------
exceldoofus's Profile: http://www.excelforum.com/member.php...o&userid=28039
View this thread: http://www.excelforum.com/showthread...hreadid=475453

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

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

Alok

"exceldoofus" wrote:


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.


--
exceldoofus
------------------------------------------------------------------------
exceldoofus's Profile: http://www.excelforum.com/member.php...o&userid=28039
View this thread: http://www.excelforum.com/showthread...hreadid=475453

On Wed, 12 Oct 2005 10:38:45 -0500, exceldoofus
wrote:


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

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


--
exceldoofus
------------------------------------------------------------------------
exceldoofus's Profile: http://www.excelforum.com/member.php...o&userid=28039
View this thread: http://www.excelforum.com/showthread...hreadid=475453