Changing date formats?

I'm pretty sure this can be done but if I tried on my own, I'll probably fail or do a lot more than I need to... so here's my problem. In one column, I have data (about 10,000 rows or more) in terms of a date format, BUT it's not really the format I want. How do I go from:

Original: 20060105
to
New: 1/5/2006

I don't want to manually change each record because it'll be too long. I tried format cells and it didn't work.

However, I noticed that if I put a "/" between the year, month, and day, like so: 2006/01/05, it automatically becomes 1/5/2006. Is there a small command/program that can insert a "/" after the 4th digit (year) and the 6th digit (month) so it can become 1/5/2006?

Please help ASAP!!!!

You might try selecting the range of data and run this:

Sub a()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = Left(Cell.Value, 4) & "/" & _
Mid(Cell.Value, 5, 2) & "/" & _
Right(Cell.Value, 2)
Next
End Sub


--
Jim
"lakegoddess" <[email protected]>
wrote in message
news:[email protected]...
|
| I'm pretty sure this can be done but if I tried on my own, I'll probably
| fail or do a lot more than I need to... so here's my problem. In one
| column, I have data (about 10,000 rows or more) in terms of a date
| format, BUT it's not really the format I want. How do I go from:
|
| Original: 20060105
| to
| New: 1/5/2006
|
| I don't want to manually change each record because it'll be too long.
| I tried format cells and it didn't work.
|
| However, I noticed that if I put a "/" between the year, month, and
| day, like so: 2006/01/05, it automatically becomes 1/5/2006. Is there a
| small command/program that can insert a "/" after the 4th digit (year)
| and the 6th digit (month) so it can become 1/5/2006?
|
| Please help ASAP!!!!
|
|
| --
| lakegoddess
| ------------------------------------------------------------------------
| lakegoddess's Profile:
http://www.excelforum.com/member.php...o&userid=18646
| View this thread: http://www.excelforum.com/showthread...hreadid=498746
|

select the column, do data>text to columns, click next twice and under
column data format select date and from dropdown select YMD
click finsih. Done!


--

Regards,

Peo Sjoblom


"lakegoddess" <[email protected]>
wrote in message
news:[email protected]...
>
> I'm pretty sure this can be done but if I tried on my own, I'll probably
> fail or do a lot more than I need to... so here's my problem. In one
> column, I have data (about 10,000 rows or more) in terms of a date
> format, BUT it's not really the format I want. How do I go from:
>
> Original: 20060105
> to
> New: 1/5/2006
>
> I don't want to manually change each record because it'll be too long.
> I tried format cells and it didn't work.
>
> However, I noticed that if I put a "/" between the year, month, and
> day, like so: 2006/01/05, it automatically becomes 1/5/2006. Is there a
> small command/program that can insert a "/" after the 4th digit (year)
> and the 6th digit (month) so it can become 1/5/2006?
>
> Please help ASAP!!!!
>
>
> --
> lakegoddess
> ------------------------------------------------------------------------
> lakegoddess's Profile:
http://www.excelforum.com/member.php...o&userid=18646
> View this thread: http://www.excelforum.com/showthread...hreadid=498746
>

For the cell you want the format changed, add a corresponding formula

=VALUE(MID(G15,1,4)&"/"&MID(G15,5,2)&"/"&MID(G15,7,2))

For the cell with the formula, set a format as described below...

Right click and select the FORMAT option.

On the number tab, select "Custom" and key in "m/d/yyyy".

Click ok. The format should be changed.

"lakegoddess" wrote:

>
> I'm pretty sure this can be done but if I tried on my own, I'll probably
> fail or do a lot more than I need to... so here's my problem. In one
> column, I have data (about 10,000 rows or more) in terms of a date
> format, BUT it's not really the format I want. How do I go from:
>
> Original: 20060105
> to
> New: 1/5/2006
>
> I don't want to manually change each record because it'll be too long.
> I tried format cells and it didn't work.
>
> However, I noticed that if I put a "/" between the year, month, and
> day, like so: 2006/01/05, it automatically becomes 1/5/2006. Is there a
> small command/program that can insert a "/" after the 4th digit (year)
> and the 6th digit (month) so it can become 1/5/2006?
>
> Please help ASAP!!!!
>
>
> --
> lakegoddess
> ------------------------------------------------------------------------
> lakegoddess's Profile: http://www.excelforum.com/member.php...o&userid=18646
> View this thread: http://www.excelforum.com/showthread...hreadid=498746
>
>

Hi,

There may well be a VBA solution, but if no one gets back to you with that,
here's a work around that will at least get you what you need.

Assuming that your data starts in A5, enter this formula in a blank column,
copy it down the 10000 rows, then copy and paste special as values over the
origianl data.

=(CONCATENATE(LEFT(A5,4),"/",MID(A5,5,2),"/",RIGHT(A5,2)))*1

-- ERR229


"lakegoddess" wrote:

>
> I'm pretty sure this can be done but if I tried on my own, I'll probably
> fail or do a lot more than I need to... so here's my problem. In one
> column, I have data (about 10,000 rows or more) in terms of a date
> format, BUT it's not really the format I want. How do I go from:
>
> Original: 20060105
> to
> New: 1/5/2006
>
> I don't want to manually change each record because it'll be too long.
> I tried format cells and it didn't work.
>
> However, I noticed that if I put a "/" between the year, month, and
> day, like so: 2006/01/05, it automatically becomes 1/5/2006. Is there a
> small command/program that can insert a "/" after the 4th digit (year)
> and the 6th digit (month) so it can become 1/5/2006?
>
> Please help ASAP!!!!
>
>
> --
> lakegoddess
> ------------------------------------------------------------------------
> lakegoddess's Profile: http://www.excelforum.com/member.php...o&userid=18646
> View this thread: http://www.excelforum.com/showthread...hreadid=498746
>
>

Wow, thanks guys! I used Peo Sjoblom's idea (short and simple) but I'm sure everything else works like magic too! Thanks so much!!!