transform text dates into Excel dates

Hell

I currently have a spreadsheet with hundreds of entries wheremany of the date entries were done textually ("21 Nov. 2002"
But I need them to be date format  Excel won't let me change the format/ won't recognize the text entries as a valid date entry.  How can I convert these entries so the Excel will permit me to change the format and recognize the entries as valid dates?

Thank yo

Daniel
0
anonymous (74717)
4/16/2004 4:46:08 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
394 Views

Similar Articles

[PageSpeed] 12

Try doing data - text to columns to separate the day, month and year. Then concatenate the values in order with a "/" in between so that it'll be a valid entry.
0
anonymous (74717)
4/16/2004 5:01:09 PM
Daniel

Data>Text to Columns>Delimited>Next>Next.  Column Data Format>Date>DMY>Finish.

No need to split and concatenate.

Gord Dibben Excel MVP

On Fri, 16 Apr 2004 09:46:08 -0700, "Daniel P"
<anonymous@discussions.microsoft.com> wrote:

>Hello
>
>I currently have a spreadsheet with hundreds of entries wheremany of the date entries were done textually ("21 Nov. 2002")
>But I need them to be date format  Excel won't let me change the format/ won't recognize the text entries as a valid date entry.  How can I convert these entries so the Excel will permit me to change the format and recognize the entries as valid dates?!
>
>Thank you
>
>Daniel

0
Gord
4/16/2004 7:20:07 PM
One more option (depending on how the month abbreviations are used).

I could just select that range/column and 
edit|replace
what:  .  (period)
with what:  (leave blank)
replace all.

xl2002 (usa version) converted them to dates--I did have to reformat to show the
way I like.

Daniel P wrote:
> 
> Hello
> 
> I currently have a spreadsheet with hundreds of entries wheremany of the date entries were done textually ("21 Nov. 2002")
> But I need them to be date format  Excel won't let me change the format/ won't recognize the text entries as a valid date entry.  How can I convert these entries so the Excel will permit me to change the format and recognize the entries as valid dates?!
> 
> Thank you
> 
> Daniel

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
4/16/2004 11:28:00 PM
Reply:

Similar Artilces:

transform data in excel
I need to transform data in an excel srpeadsheet. I need to reverse a 5 rows, 200 columns table into a 5 columns, 200 rows table. Much appreciated!Vincent Hi Vincent Copy the set of data. Got to your destination and choose Paste Special and click the box marked Transpose. -- Regards Roger Govier "Vincent Yang" <vyang69@verizon.net> wrote in message news:MVORb.1866$px6.279@nwrddc02.gnilink.net... > I need to transform data in an excel srpeadsheet. I need to reverse a > 5 rows, 200 columns table into a 5 columns, 200 rows table. > > Much appreciated!Vincent >...

What is fastest hardware for Excel?
Please don't be upset about the general nature of this message. I need to increase the speed of my Excel spreadsheet. What can I do to improve speed? I'm running a 2.4GHz machine with 1GB RAM, Windows 2000. Can someone suggest a machine? Price isn't important, within reason of course. The cost of rewriting the sheet in C++ will likely outweigh the cost of buying a suped-up PC. TIA, Mike. That already sounds like a pretty fast machine. In general, useful tips about speed, memory usage etc can be found at www.decisionmodels.com -- Kind Regards, Niek Otten Microsoft M...

If E9 Is red then place tommorrows date in A11
XL-2000 Is there a formula that can If E9 Is red then place tomorrow's date in A11 or If E9 Is green then place tomorrow's date in A11 Allan Is this a typo or did you mean "if E9 is green or red place tomorrow's date in A11"? Perhaps one of the "tomorrows" should be "today". How does E9 get to be green or red? Conditional Formatting? If so, use the same condition in a formula in A11. If you manually format E9 to be green or red, you will have to use VBA to return whichever date in A11. Some more detail maybe? Gord Dibben Excel MVP - XL9...

Irregular Text Shapes
I have a problem doing something that seems like it should be simple, but it's got me stumped. I'm formatting a book in Publisher 2007. I've used PageMaker in the past, but my version is now too old (PM65) and since Publisher came with my Office suite I thought I'd give it a try rather than spending $$$ for the Adobe upgrade. I was pleasantly surprised how easy Publisher was to learn. I'm currently laying out the cover and the liner notes on the back go into an irregularly shaped area in the artwork. I've just spent over two hours trying to figure a way to modif...

Why when I print out a spreadsheet on Excel it doesn't have lines
Please tell me how to get lines(rows and columns) on my paper when I print out my spreadsheet using excel? Go to the File menu and choose Page Setup. There, select the Sheet tab and put a check mark in the Gridlines checkbox. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "johnson001" <johnson001@discussions.microsoft.com> wrote in message news:FB9FAA5D-7856-4680-8F08-BB0D999F5C05@microsoft.com... > Please tell me how to get lines(rows and columns) on my paper > when I print > out my spreadsheet using excel...

how do I type text in text box in Publisher
I am trying to create a banner in Microsoft Publisher. I have created a tex block but when I try to type text in the block it won't let me. When I start typing nothing shows up in text block Have you selected the text box? And if so, do you see a flashing cursor in the text box? -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "Challenged" <Challenged@discussions.microsoft.com> wrote in message news:4F364F4C-16D4-4136-9389-A9836AD0934A@microsoft.com... >I ...

Export a PRN file in Excel
I have an excel spreadsheet with columns A - AC and I need to save it in formatted text space delimited. When I do save as and select formatted text space delimited the file wraps at 255 characters. Is there anyway to get around this? I need the entire row to be on one line followed by the next row on the next line. (.prn files will wrap after 240 characters) You have a few choices (try against a copy of your worksheet): I'd either concatenate the cell values into another column: =LEFT(A1&REPT(" ",20),20) & LEFT(B1&REPT(" ",10),10) & TEXT(C1,&q...

Errors List re: Excel XP?
Anyone have a comprehensive of Access 2002 error messages and solutions that they would be willing to share? Also, what about advisories i.e., "to avoid this - do that" type of information? What ever information I receive, I will compile and share with others in the group. Thanks in advance! Dennis dmburgess@ameritech.net ...

Date and Time ??
I had a question working about date and time it had 11 or 12 posts and now it's gone. Any Idea what happened to it? Sorry I found it. "Duane" wrote: > I had a question working about date and time it had 11 or 12 posts and now > it's gone. > > Any Idea what happened to it? "Duane" <Duane@discussions.microsoft.com> wrote in message news:9F0A6769-90FA-4E91-81AF-22F2B7549C32@microsoft.com... >I had a question working about date and time it had 11 or 12 posts and >now > it's gone. > > Any Idea what happen...

Text Field
Hi, I want to create a text field for users to enter in Excel. The amoun of text can be pretty large especially for case recordings. One of th users complained that Excel does not work like Word, where the text wil go to the next line automatically. For Excel, the text spill over. Yo would have to arrange them for printing, do line-break manually. tried wrap text and merge cells, it worked well for the first fe lines. For the rest of the text, it became messy. And it cant displa the whole text, the max row height is 409 -- mejame -----------------------------------------------------------...

How do I make rows expand to fit wrapped text in cells?
I am submitting a grant which must be done in Excel. The grant was originally written in Word, and I am cutting and pasting, a paragraph at a time, into Excel. When I do Format -> Row -> Autofit, it shrinks the cell to one line, regardless of how many lines of wrapped text are in there. I'm on a deadline and really need help fast... If you have a lot to convert, I'd save the original word file as a text file and then open that into excel. I'd use the "DELIMITED" choice and ensure that all of the delimiters are turned off. Once you have the data in excel...

Run-time error '-2146233079 (80131509)' in the Excel Application
I have created a Macros to view the Analtyical Reports in the Excel. When the user is trying to get the data from the server he is getting an error message though i am able to perform the operation successfully on my env. Error Message: Run-time error '-2146233079 (80131509)': The underlying connection was closed: The connection was closed unexpectedly. As we clik on the ok button for the above error message pop-up, i am getting an another error message with the following details. It has two buttons defined on the pop-up:- Cancel and Continue. Then this error message appeared: Mi...

Text cursor disappears while typing (Outlook 2007)
While typing in outlook 2007, the cursor will disappear most of the time and blinks on every few letters. (I'm not talking about automatically hiding the mouse cursor/pointer while tying, which is available in the Mouse visibility options). I mean the text cursor. This behavior also appears using the print view in Word 2007 (in the draft view the cursor is visible while typing). Most other applications including MS apps (and office 2003) do not behave this way. The most obvious way to show the behavior is to hold the space bar while typing an email in outlook 2007. You wi...

excel shared file
Hi, Is there any way to track the activity on a shared excel file? Can it be done in a VBA code? I would appreciate any help Thank you Gabriel How about the built in Tools|Track Changes (and this requires the workbook to be shared, too!) Gabriel wrote: > > Hi, > > Is there any way to track the activity on a shared excel file? Can it > be done in a VBA code? > > I would appreciate any help > Thank you > > Gabriel -- Dave Peterson ec35720@msn.com Actually I'm interested in building something like an activity log. For instance, Mr Y is accessing th...

LEVEY-JENNINGS CHARTS ON EXCEL
Does anyone know the function of creating a levey-jennings chart on Excel? Does this help: http://peltiertech.com/Excel/Charts/RunChtLines.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "whoakj" <whoakj@discussions.microsoft.com> wrote in message news:19D61FB6-AD18-4B0F-BFEE-EBEDC76C6692@microsoft.com... > Does anyone know the function of creating a levey-jennings chart on Excel? ...

help with macro to read and sort data from multiple text files
Hello there !! I have x number of ascii text files that have space delimited data columns (files may have 3 to 40 data columns). There are about 25 header rows at the top of each file but I am not going to use info from these rows. I also have a master.xls file. Could someone please help me create a macro to do the following - A) read first *.txt file from the given folder into sheet1 of the master.xls file. NOTE: I have already set up the sheet2 of the master.xls to sort required rows of data from sheet1 (based on row headers) using vlookup to populate a 40 column wide array. B) Copy the ...

How to XslTransform.Transform to new web page from code behind?
I'm working with a typed dataset and want to implement a print button that prints my formatted data without the company logo and other stuff on the form. I've created an XSLT file to transform the XML version of the dataset into HTML and would like to display it in a new browser window leaving the original window up and/or maybe even just send it out to the default printer or bring up the print dialog for printing out the web page. I have successfully saved the html file, but I'm not sure how to send to a new browser window instead of a file. Please help, code below: XmlData...

encrypt text as valid ascii character
do you know a method to encrypt a text and get only apha- numeric characters, without simbols, just A-Z, a-a 0-9 characters. thanks in advance for your help, Eduardo On Mon, 20 Oct 2003 15:37:00 -0700, Eduardo wrote: > do you know a method to encrypt a text and get only apha- > numeric characters, without simbols, just A-Z, a-a 0-9 > characters. Not if you want it to be secure. You could use a real crypto, e.g. AES, bluetoth, ... and then use uuencode to make it 7-bit ascii (A-z 0-9) (to decrypt you need to uudecode first and then use the crypto) If you don't need a secure...

Add text to bar charts
hi have a series of bar charts want to add text to these, can anybody help? The text that I want to add is the same as the legend if that helps thanks in advance paddy Click the chart and start typing - this should add a text box. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "mcgrath383" <mcgrath383@gmail.com> wrote in message news:1159301697.671691.184350@e3g2000cwe.googlegroups.com... > hi > have a series of bar charts want to add text to these, can anybody > help? > The text that I want to add is the same as the legend ...

Displaying a Text File in an Edit Box
Hi All Is it possible to display the contents of a Text file in an Edit box. Edit Box name is ID_SHOW_TRADE Variable for this edit box is a CString called strTrade The file name is TRADE.DAT & is saved in Text format. Help would be much appreciated Jay. void CMyDialog::ReadFile() { CStdioFile In; if (!In.Open("Trade.Dat",CFile::modeRead)) { return; //could not open file } CString Temp; strTrade = ""; while (In.ReadString(Temp)) { strTrade += Temp; } In.Close(); UpdateData(FALSE); } Or you can do it like this void CMyDialog::ReadFile() { CStdioFile I...

Excel 2007 Polynomial Order Incorrect
We recently upgraded to Office 2007. When we attempt to do a trendline in Excel, the order of polynomials is always one below what it is stated to be and the highest ordered polynomial is always wrong. (i.e. on a fourth order polynomial, it will only display polynomials through the third order and in the equation, the third ordered polynomial value is wrong, the others are correct). We tried installing the hot fix from KB 938541 as well as right clicking on the equation for the chart and adding significant digits under Format Trendline Label. A sample file showing the problem can be d...

Excel hogs resources
This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C4CAED.0FB3FB90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there a way to allocate/limit resources for Excel. I have several = formulas I run (and drag down thousands of rows) and I can't use my PC = for about 20 minutes while these formulas are running because Excel = takes up 99% of my CPU. Is there an easy way to limit how much resources = Excel uses? I realize the formulas will take longer to run, but at least = I could perform other tasks while Excle...

publisher insists on underlining copied text ? do i remove it
I copied text from a Word Document and when it appeares in Publisher it is always underlined and i can't seem to remove the underline. Any ideas? Angelis <Angelis@discussions.microsoft.com> was very recently heard to utter: > I copied text from a Word Document and when it appeares in Publisher > it is always underlined and i can't seem to remove the underline. Paste Special as Unformatted Text? -- Ed Bennett - MVP Microsoft Publisher All the text? Try "paste special", unformatted text. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.m...

how do I make the cell expand to fit my text as I type?
I merged some cells across the width of a spreadsheet and i can't get the cell to increase it's height as more text is typed in wraps around. Only way I know, is to move your cursor to between the row number of therow you want to adjust and the next row. The cursor changes shape into an up/down arrow. Click and drag till you see all. Double clicking will also not work "pnwwoman" wrote: > I merged some cells across the width of a spreadsheet and i can't get the > cell to increase it's height as more text is typed in wraps around. If cells are merged. ...

How to Justify Text
My Publisher (2002) help does not contain the instructions for justifying text - could someone please tell me how to do this? I've done a search in this newsgroup but it seems no-one else has this problem :) Jan M > My Publisher (2002) help does not contain the instructions > for justifying text - could someone please tell me how to > do this? > > I've done a search in this newsgroup but it seems no-one > else has this problem > > Jan M Do you have your formatting toolbar turned on? From left to right, mine has boxes for font selection, formattin...