Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert numbers to date: "586" to read "May 1986"
I have a huge spreadsheet where all the dates in the date column are listed
as myy. For example "586" should be May 1986, and "801" should be August 2001. Is there any way to convert the whole column so i dont' have to manually change them? If it helps, the file was an Access file my client burned toa CD. I opened it on another computer and this was how the column appeared, so I'mnot sure if its a formatting error on his end, or if that is how he actually entered the data. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert numbers to date: "586" to read "May 1986"
Let's say the dates are in column C and the first one is in C2.
Insert a new column D (we will get rid of it soon!) In D2 enter =DATE(IF(MOD(C2,100)<10,MOD(C2,100)+2000,MOD(C2,10 0)+1900),INT(C2/100),1) Now format it with Custom Format of <mmmm yyyy or just <mmm yyyy for three-letter month names Copy this down the column - quickest way is to double click D2's fill handle which is the small solid square in lower right corner of a active cell. Select all of the D column and copy; with it still selected use Edit | Paste Special :Values. Now the D data is a real dates not a formulas so you can delete column C, making the D column the C column with the dates you want. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "CEckels" wrote in message ... I have a huge spreadsheet where all the dates in the date column are listed as myy. For example "586" should be May 1986, and "801" should be August 2001. Is there any way to convert the whole column so i dont' have to manually change them? If it helps, the file was an Access file my client burned toa CD. I opened it on another computer and this was how the column appeared, so I'mnot sure if its a formatting error on his end, or if that is how he actually entered the data. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert numbers to date: "586" to read "May 1986"
"CEckels" wrote:
I'mnot sure if its a formatting error on his end, or if that is how he actually entered the data. Of course, you should determine that first. Select a cell, right-click and click Format Number. If you see a Custom format of the form "myy", select the entire column of dates, right-click and click Format Number, then select Custom format and enter "mmm yyyy" without the quotes. Otherwise.... Is there any way to convert the whole column so i dont' have to manually change them? One way.... In a parallel column, enter the following formula and copy down as needed: =datevalue(left(A1,len(A1)-2) & "/1/" & right(A1,2)) assuming the first bogus date (e.g. 586) is in A1. Then select the new column of dates and press ctrl-C to copy. Then select the original column of dates, right-click and click Paste Special Value OK. You can now delete the new column of dates. PS: One way to select a large range of cells is to enter the range in the Name Box in the upper left corner above the worksheet. ----- original message ----- "CEckels" wrote in message ... I have a huge spreadsheet where all the dates in the date column are listed as myy. For example "586" should be May 1986, and "801" should be August 2001. Is there any way to convert the whole column so i dont' have to manually change them? If it helps, the file was an Access file my client burned toa CD. I opened it on another computer and this was how the column appeared, so I'mnot sure if its a formatting error on his end, or if that is how he actually entered the data. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert numbers to date: "586" to read "May 1986"
PS....
I wrote: Then select the new column of dates and press ctrl-C to copy. Then select the original column of dates, right-click and click Paste Special Value OK. I neglected to add: and format the column of dates with the Custom "mmm yyyy" format. ----- original message ----- "JoeU2004" wrote in message ... "CEckels" wrote: I'mnot sure if its a formatting error on his end, or if that is how he actually entered the data. Of course, you should determine that first. Select a cell, right-click and click Format Number. If you see a Custom format of the form "myy", select the entire column of dates, right-click and click Format Number, then select Custom format and enter "mmm yyyy" without the quotes. Otherwise.... Is there any way to convert the whole column so i dont' have to manually change them? One way.... In a parallel column, enter the following formula and copy down as needed: =datevalue(left(A1,len(A1)-2) & "/1/" & right(A1,2)) assuming the first bogus date (e.g. 586) is in A1. Then select the new column of dates and press ctrl-C to copy. Then select the original column of dates, right-click and click Paste Special Value OK. You can now delete the new column of dates. PS: One way to select a large range of cells is to enter the range in the Name Box in the upper left corner above the worksheet. ----- original message ----- "CEckels" wrote in message ... I have a huge spreadsheet where all the dates in the date column are listed as myy. For example "586" should be May 1986, and "801" should be August 2001. Is there any way to convert the whole column so i dont' have to manually change them? If it helps, the file was an Access file my client burned toa CD. I opened it on another computer and this was how the column appeared, so I'mnot sure if its a formatting error on his end, or if that is how he actually entered the data. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert numbers to date: "586" to read "May 1986"
Try this:
=--REPLACE(A1,LEN(A1)-1,,"/1/") Format cell as: mmmm yyyy "CEckels" wrote: I have a huge spreadsheet where all the dates in the date column are listed as myy. For example "586" should be May 1986, and "801" should be August 2001. Is there any way to convert the whole column so i dont' have to manually change them? If it helps, the file was an Access file my client burned toa CD. I opened it on another computer and this was how the column appeared, so I'mnot sure if its a formatting error on his end, or if that is how he actually entered the data. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert numbers to date: "586" to read "May 1986"
Many thanks to all who replied. All of your suggestions worked.
You've saved me hours!! "CEckels" wrote: I have a huge spreadsheet where all the dates in the date column are listed as myy. For example "586" should be May 1986, and "801" should be August 2001. Is there any way to convert the whole column so i dont' have to manually change them? If it helps, the file was an Access file my client burned toa CD. I opened it on another computer and this was how the column appeared, so I'mnot sure if its a formatting error on his end, or if that is how he actually entered the data. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
any formula to convert numbers in word form, e.g. "2" as "Two"? | Excel Worksheet Functions | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |