I have data Imported into cells from another program. The data is a date,
and it comes into the cell as follows:

April, 2006 = 406
December, 2006 = 1206
etc, etc...

Now, I want to format the cells such that they change to

Apr-06
Dec-06

I have been messing around trying to figure out a custom format, and have
had no luck. What am I missing? Ultimately I'll incorporate this into a
macro, SO, if there are some CODE solutions, bring em on. THANKS!!!!!!!

I think I this conversion may not be so tough to make.

First we peel off the first three characters simply by using the
=LEFT() function, then store that in an adjacent cell. Then peel off
the "06" part by using the =RIGHT() function and store that in another
cell. Then use the CONCATENATE function to join the contents of the
first cell, plus a dash, plus the contents of the second cell.

Taking it a notch further, you can nest the RIGHT and LEFT functions
within the CONCATENATE function, and not have to store those substrings
at all.

-Dan


--
vthoky
------------------------------------------------------------------------
vthoky's Profile: http://www.excelforum.com/member.php...o&userid=34607
View this thread: http://www.excelforum.com/showthread...hreadid=543784

I'm guessing that your dates were imported as Text fields, rather than dates.
Thus, no matter what Date Format you apply, you won't see any changes. This
should be identifiable by an apostrophe at the beginning of each date that is
only visible in the Formula Bar, not the cell.

Follow these steps to remove the apostrophes.

Enter a 1 into any blank cell.
Copy that cell.
Select your range of dates.
Paste Special
Check the box called "Multiply"
Click OK
Delete the 1 you entered originally.
Now, try applying your desired Date Format and see if it works.

HTH,
Elkar


"bodhisatvaofboogie" wrote:

I have data Imported into cells from another program. The data is a date,
and it comes into the cell as follows:

April, 2006 = 406
December, 2006 = 1206
etc, etc...

Now, I want to format the cells such that they change to

Apr-06
Dec-06

I have been messing around trying to figure out a custom format, and have
had no luck. What am I missing? Ultimately I'll incorporate this into a
macro, SO, if there are some CODE solutions, bring em on. THANKS!!!!!!!

The Concatenate function doesn not allow it to format into mmyy. Or perhaps
I am doing something wrong.

"vthoky" wrote:


I think I this conversion may not be so tough to make.

First we peel off the first three characters simply by using the
=LEFT() function, then store that in an adjacent cell. Then peel off
the "06" part by using the =RIGHT() function and store that in another
cell. Then use the CONCATENATE function to join the contents of the
first cell, plus a dash, plus the contents of the second cell.

Taking it a notch further, you can nest the RIGHT and LEFT functions
within the CONCATENATE function, and not have to store those substrings
at all.

-Dan


--
vthoky
------------------------------------------------------------------------
vthoky's Profile: http://www.excelforum.com/member.php...o&userid=34607
View this thread: http://www.excelforum.com/showthread...hreadid=543784

Perhaps I misunderstood. In your last message you mention mmyy format,
but in the original one your example was Apr-06. I was using that
format for the example I gave you. Assuming the value "April, 2006"
was in cell D3, the formula I put in F3 was
=CONCATENATE((LEFT(D3,3)),"-",(RIGHT(D3,2)))

If you want to go to the mmyy format, then we have some more work to
do. Getting the yy portion is easy. Having Excel determine the month
name from the given text is what we need to do next. Once we have
that, then it's back to the Concatenate statement and all is well.
Please let me know if that's what you're looking for, and I'll see if I
can make it work.

-Dan


--
vthoky
------------------------------------------------------------------------
vthoky's Profile: http://www.excelforum.com/member.php...o&userid=34607
View this thread: http://www.excelforum.com/showthread...hreadid=543784

What was originally in the cells after the import is the single numbers, the
406 which stands for April 2006. BUT it is just listed as 406. I want to
convert that 406 into Apr-06 That is my ideal solution :)

Example:

In Cell Want it to become
406 Apr-06
1205 Dec-05

etc.....



"vthoky" wrote:


Perhaps I misunderstood. In your last message you mention mmyy format,
but in the original one your example was Apr-06. I was using that
format for the example I gave you. Assuming the value "April, 2006"
was in cell D3, the formula I put in F3 was
=CONCATENATE((LEFT(D3,3)),"-",(RIGHT(D3,2)))

If you want to go to the mmyy format, then we have some more work to
do. Getting the yy portion is easy. Having Excel determine the month
name from the given text is what we need to do next. Once we have
that, then it's back to the Concatenate statement and all is well.
Please let me know if that's what you're looking for, and I'll see if I
can make it work.

-Dan


--
vthoky
------------------------------------------------------------------------
vthoky's Profile: http://www.excelforum.com/member.php...o&userid=34607
View this thread: http://www.excelforum.com/showthread...hreadid=543784

I'm to take it my solution didn't work? But, here's another idea, using a
second column enter this formula:

=TEXT(A1,"mmm-yy")

HTH,
Elkar


"bodhisatvaofboogie" wrote:

What was originally in the cells after the import is the single numbers, the
406 which stands for April 2006. BUT it is just listed as 406. I want to
convert that 406 into Apr-06 That is my ideal solution :)

Example:

In Cell Want it to become
406 Apr-06
1205 Dec-05

etc.....



"vthoky" wrote:


Perhaps I misunderstood. In your last message you mention mmyy format,
but in the original one your example was Apr-06. I was using that
format for the example I gave you. Assuming the value "April, 2006"
was in cell D3, the formula I put in F3 was
=CONCATENATE((LEFT(D3,3)),"-",(RIGHT(D3,2)))

If you want to go to the mmyy format, then we have some more work to
do. Getting the yy portion is easy. Having Excel determine the month
name from the given text is what we need to do next. Once we have
that, then it's back to the Concatenate statement and all is well.
Please let me know if that's what you're looking for, and I'll see if I
can make it work.

-Dan


--
vthoky
------------------------------------------------------------------------
vthoky's Profile: http://www.excelforum.com/member.php...o&userid=34607
View this thread: http://www.excelforum.com/showthread...hreadid=543784

Sorry, I misunderstood.

Here's my example:
* In cells F14 through F12 input the values 106, 206, ..., 1206
* In cells J1=J12 I have the numbers 1 through 12
* In cells K1 through K12 I have the three-character month names (Jan,
Feb, etc.)
* In cell G14 I have =LEFT(F14,LEN(F14)-2). This gives us the month
number.
* In cell H14 I have =LOOKUP(1,J$1:J$12,K$1:K$12). This refers to the
table in J1:K12 for lookup values.
* In cell I14 I have =CONCATENATE(H14,"-",(RIGHT(F14,2))). Copy this
cell down through I25. This cell gathers the month number (derived in
H14) plus the hyphen, plus the last two digits in the original field
(the year number) and puts them all together into your desired format.

The formulas in Cells H15-H25 will be like the one in H14, except
they'll start with =LOOKUP(2,... then =LOOKUP(3,... and so forth
through =LOOKUP(12,... .

It's sort of big-hammer, but it works.

I'd be glad to email you the file if you'd like.

-Dan


--
vthoky
------------------------------------------------------------------------
vthoky's Profile: http://www.excelforum.com/member.php...o&userid=34607
View this thread: http://www.excelforum.com/showthread...hreadid=543784