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.

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
<[email protected]> 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!!!