Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!! -- lakegoddess ------------------------------------------------------------------------ lakegoddess's Profile: http://www.excelforum.com/member.php...o&userid=18646 View this thread: http://www.excelforum.com/showthread...hreadid=498746 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing date formats?
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" wrote in message ... | | 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 | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing date formats?
select the column, do datatext to columns, click next twice and under
column data format select date and from dropdown select YMD click finsih. Done! -- Regards, Peo Sjoblom "lakegoddess" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing date formats?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing date formats?
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing date formats?
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!!! -- lakegoddess ------------------------------------------------------------------------ lakegoddess's Profile: http://www.excelforum.com/member.php...o&userid=18646 View this thread: http://www.excelforum.com/showthread...hreadid=498746 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing cell contents so that Excel recognises it as a date | Excel Discussion (Misc queries) | |||
Date Formats | Excel Discussion (Misc queries) | |||
How do I stop excel automatically changing my date to 2005? | Excel Discussion (Misc queries) | |||
Changing date serial numbers | Excel Discussion (Misc queries) | |||
Need to find oldest date in ever changing list. | Excel Worksheet Functions |