I can't format 01/19/2004 to read January 19, 2004, please help!!

Dragged and dropped a series of dates and now I can't convert them to sort 
them properly.  What to do??
0
6/16/2005 11:19:02 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
304 Views

Similar Articles

[PageSpeed] 3

Hi Paul,

"paulonline66" <paulonline66@discussions.microsoft.com> wrote in message 
news:3BB832DA-34AE-44B4-9D73-6F178E9AEBF0@microsoft.com...
> I can't format 01/19/2004 to read January 19, 2004, please help!!
> Dragged and dropped a series of dates and now I can't convert them to sort
> them properly.  What to do??


With the date cells selected:

 Data | Text to Columns | Next | Next | Select the Date option and Select 
the DMY option in the dropdown box | Finish

---
Regards,
Norman


0
normanjones (1047)
6/17/2005 12:54:01 AM
Hello Norman,

I followed your instructions to the letter several times without any 
success.  I've pasted the values as "values", without changing any format, 
and I'm unable to reformat them at all.  Would this have anything to do with 
the "/"?  

Thanks for your assistance, I think I'm getting closer....

"Norman Jones" wrote:

> Hi Paul,
> 
> "paulonline66" <paulonline66@discussions.microsoft.com> wrote in message 
> news:3BB832DA-34AE-44B4-9D73-6F178E9AEBF0@microsoft.com...
> > I can't format 01/19/2004 to read January 19, 2004, please help!!
> > Dragged and dropped a series of dates and now I can't convert them to sort
> > them properly.  What to do??
> 
> 
> With the date cells selected:
> 
>  Data | Text to Columns | Next | Next | Select the Date option and Select 
> the DMY option in the dropdown box | Finish
> 
> ---
> Regards,
> Norman
> 
> 
> 
0
6/17/2005 2:51:03 AM
Hi Paul,

I copied 01/19/2004 from your posted line:

   > I can't format 01/19/2004 to read January 19, 2004, please help!!

into a number of cells in a column. I then :

1)  Selected Text to Columns on the Data menu (with the cells selected!)
2) Checked the 'Fixed Width" option
3) Hit the 'Next' button
4) Hit the 'Next' button
5) Checked the 'Date' option
6) Selected 'MDY' in the dropdown
7) Hit the 'Finish' button

This converted the original text values to dates which I was then able to 
format as desired.


---
Regards,
Norman



"paulonline66" <paulonline66@discussions.microsoft.com> wrote in message 
news:B098E0EF-A2A6-45B7-8E95-FEF47CEF1B91@microsoft.com...
> Hello Norman,
>
> I followed your instructions to the letter several times without any
> success.  I've pasted the values as "values", without changing any format,
> and I'm unable to reformat them at all.  Would this have anything to do 
> with
> the "/"?
>
> Thanks for your assistance, I think I'm getting closer....
>
> "Norman Jones" wrote:
>
>> Hi Paul,
>>
>> "paulonline66" <paulonline66@discussions.microsoft.com> wrote in message
>> news:3BB832DA-34AE-44B4-9D73-6F178E9AEBF0@microsoft.com...
>> > I can't format 01/19/2004 to read January 19, 2004, please help!!
>> > Dragged and dropped a series of dates and now I can't convert them to 
>> > sort
>> > them properly.  What to do??
>>
>>
>> With the date cells selected:
>>
>>  Data | Text to Columns | Next | Next | Select the Date option and Select
>> the DMY option in the dropdown box | Finish
>>
>> ---
>> Regards,
>> Norman
>>
>>
>> 


0
normanjones (1047)
6/17/2005 3:16:47 AM
Hello Norman,

Y'know, I think I've discovered the problem.  You were absolutely right of 
course, once I found out what else was going on.  

It seems that the dates that I copied from an online source have a space 
placed before and after the value (which I've been unable to eliminate 
without going through each cell seperately and deleting it).  For example, 
"1/19/2004" is actually being pasted as " 1/19/2004 ".  Once the space has 
been removed from in front of the value (rear space doesn't seem to matter), 
I'm able to follow your instructions and it works like a charm.  Sorry for 
doubting you!!!

By the way, if you happen to know, how can I get rid of these damn spaces 
efficiently I'd be eternally grateful!

Thanks for your patience and persistence!

Paul

"Norman Jones" wrote:

> Hi Paul,
> 
> I copied 01/19/2004 from your posted line:
> 
>    > I can't format 01/19/2004 to read January 19, 2004, please help!!
> 
> into a number of cells in a column. I then :
> 
> 1)  Selected Text to Columns on the Data menu (with the cells selected!)
> 2) Checked the 'Fixed Width" option
> 3) Hit the 'Next' button
> 4) Hit the 'Next' button
> 5) Checked the 'Date' option
> 6) Selected 'MDY' in the dropdown
> 7) Hit the 'Finish' button
> 
> This converted the original text values to dates which I was then able to 
> format as desired.
> 
> 
> ---
> Regards,
> Norman
> 
> 
> 
> "paulonline66" <paulonline66@discussions.microsoft.com> wrote in message 
> news:B098E0EF-A2A6-45B7-8E95-FEF47CEF1B91@microsoft.com...
> > Hello Norman,
> >
> > I followed your instructions to the letter several times without any
> > success.  I've pasted the values as "values", without changing any format,
> > and I'm unable to reformat them at all.  Would this have anything to do 
> > with
> > the "/"?
> >
> > Thanks for your assistance, I think I'm getting closer....
> >
> > "Norman Jones" wrote:
> >
> >> Hi Paul,
> >>
> >> "paulonline66" <paulonline66@discussions.microsoft.com> wrote in message
> >> news:3BB832DA-34AE-44B4-9D73-6F178E9AEBF0@microsoft.com...
> >> > I can't format 01/19/2004 to read January 19, 2004, please help!!
> >> > Dragged and dropped a series of dates and now I can't convert them to 
> >> > sort
> >> > them properly.  What to do??
> >>
> >>
> >> With the date cells selected:
> >>
> >>  Data | Text to Columns | Next | Next | Select the Date option and Select
> >> the DMY option in the dropdown box | Finish
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >> 
> 
> 
> 
0
6/17/2005 3:46:00 AM
Hi Paul,

> By the way, if you happen to know, how can I get rid of these damn spaces
> efficiently I'd be eternally grateful!

See David McRitchie's TrimAll sub at:

      http://www.mvps.org/dmcritchie/excel/join.htm - trimall

Additionally, if you are not familiar with macros, see David McRitchie's
introduction notes at:

        http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman



"paulonline66" <paulonline66@discussions.microsoft.com> wrote in message 
news:B7E663AA-96A4-44CA-B11E-5BBE527D3283@microsoft.com...
> Hello Norman,
>
> Y'know, I think I've discovered the problem.  You were absolutely right of
> course, once I found out what else was going on.
>
> It seems that the dates that I copied from an online source have a space
> placed before and after the value (which I've been unable to eliminate
> without going through each cell seperately and deleting it).  For example,
> "1/19/2004" is actually being pasted as " 1/19/2004 ".  Once the space has
> been removed from in front of the value (rear space doesn't seem to 
> matter),
> I'm able to follow your instructions and it works like a charm.  Sorry for
> doubting you!!!
>
> By the way, if you happen to know, how can I get rid of these damn spaces
> efficiently I'd be eternally grateful!
>
> Thanks for your patience and persistence!
>
> Paul
>
> "Norman Jones" wrote:
>
>> Hi Paul,
>>
>> I copied 01/19/2004 from your posted line:
>>
>>    > I can't format 01/19/2004 to read January 19, 2004, please help!!
>>
>> into a number of cells in a column. I then :
>>
>> 1)  Selected Text to Columns on the Data menu (with the cells selected!)
>> 2) Checked the 'Fixed Width" option
>> 3) Hit the 'Next' button
>> 4) Hit the 'Next' button
>> 5) Checked the 'Date' option
>> 6) Selected 'MDY' in the dropdown
>> 7) Hit the 'Finish' button
>>
>> This converted the original text values to dates which I was then able to
>> format as desired.
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "paulonline66" <paulonline66@discussions.microsoft.com> wrote in message
>> news:B098E0EF-A2A6-45B7-8E95-FEF47CEF1B91@microsoft.com...
>> > Hello Norman,
>> >
>> > I followed your instructions to the letter several times without any
>> > success.  I've pasted the values as "values", without changing any 
>> > format,
>> > and I'm unable to reformat them at all.  Would this have anything to do
>> > with
>> > the "/"?
>> >
>> > Thanks for your assistance, I think I'm getting closer....
>> >
>> > "Norman Jones" wrote:
>> >
>> >> Hi Paul,
>> >>
>> >> "paulonline66" <paulonline66@discussions.microsoft.com> wrote in 
>> >> message
>> >> news:3BB832DA-34AE-44B4-9D73-6F178E9AEBF0@microsoft.com...
>> >> > I can't format 01/19/2004 to read January 19, 2004, please help!!
>> >> > Dragged and dropped a series of dates and now I can't convert them 
>> >> > to
>> >> > sort
>> >> > them properly.  What to do??
>> >>
>> >>
>> >> With the date cells selected:
>> >>
>> >>  Data | Text to Columns | Next | Next | Select the Date option and 
>> >> Select
>> >> the DMY option in the dropdown box | Finish
>> >>
>> >> ---
>> >> Regards,
>> >> Norman
>> >>
>> >>
>> >>
>>
>>
>> 


0
normanjones (1047)
6/17/2005 3:54:37 AM
Reply:

Similar Artilces:

Conditional Formula formatting
I'm trying to get a cell color coded (B36) based on a formula result in another cell (H3), based on several different parameters, i.e.: If H3>60, I want cell B36 background to turn green If H3 <=60 AND => 30, I want cell B36 background to turn yellow If H3 <30, I want cell B36 background to turn red I've tried multiple conditional formatting, IF functions, and simple formulas and nothing seems to work -- I don't get any color formatting no matter what formula variation I try. Any suggestions? Select cell B36 and go to the conditional format dialog b...

HELP! Emails
Having problems with windows xp, system is crashing. I trying to backup all of my files on to a CD. I can't locate the files that contain my inbox. Outlook express not working. Can anyone tell me where I can find these files in my system. Thank you Hi, Bobbie: This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in one of these newsgroups: mic...

"can't exit design mode because..." error
For some reason I am getting an error that says "Can't exit design mode because 'CommandButton1' could not be created" There is no CommandButton1 any more (I deleted it) and there is no code referring to this button. How can I make it stop? Thanks. ...

Commissions data base HELP
I need help programing a data base to figuare out commisions. I need a data base to multiply 0-250 by $.50 then from 251-500 by $.65 then from 501-750 by $.75 then from 751-1000 by $.85 then from 1001-1500 by $1.00 then from 1501-2000 by $1.15 and then from 2001 + by $1.25. So if someone would enter 2000 I need the data base to multiply the first 250 by $.50 then the next 250 by $.65 then the next 250 by $.75 and so on. Any suggestions will help. Thanks Jason One way: =SUMPRODUCT(--(A1>{0,250,500,750,1000,1500,2000}), (A1-{0,250,500,750,1000,1500,2000}), {0.5,0.15,0.1,0.1,0.15...

thread reading serial port uses 100% cpu
Hi, I’m writing a SDI program that communicates with serial port, I’m using CreateFile and ReadFile in overlapped mode and I use CreateThread to run it in a closed loop and call a function from CDocument whenever a byte arrives. Well, everything seems ok but one thing! Program runs and its responsiveness seems ok, but CPU usage reaches 100% whenever program runs(no matter of whether there is a stream of bytes arriving or not). I think I missed something, because whenever serial programs like mttty or hyper terminal runs, there is no significant increase in CPU usage. I tried to implemen...

re help
hi i have just installed vista on my comp and all is working well im using windows mail and just wanted some more stationery so i went to down load Microsoft's own but can i find it when it installs it it does not put it into the windows mail stationery file ? why not any help ...

HELP: problems using old version of Outlook....
Does anyone still use Outlook 97? I am having problems trying to setup an email account that uses POP/SMTP and also SPA for authentication for SMTP. POP/SMTP doesn't seem to be one of the available options in Outlook 97. The only email account types are MS Exchange, MS Mail, and something else but no SMTP or POP. Do I have to install some addon or plugins? It has to be brought up-to-date to be useful. Be sure you installed SR1 and SR2 and Internet E-mail Enhancement Patch (IMEP) for Outlook 97, then use the setup instructions for Outlook 98 found here: OL98: (CW) Configuring the ...

Need help with error message #3
I recieve the following error message when I try to import a .csv file from my desktop to my outlook 2000. I'm not sure how to add the correct translators, or fix the error. "A file error has occured in the comma seperated values (windows) translator while initilizing a translator to build a field map" ...

contacts macro help!
Hi there I want to create a macro within excel that can take cells containing name, company name, telephone number and email, and add them to my contacts database in outlook should I choose to. Before it adds the data, it will need to check if the data doesn't already exist. I'm using Office XP. thanks in advance ...

2004 Seriously slows XP performance.
After installing Money 2004 Deluxe, pages on the internet loaded and opened at a snail's pace. The closest anwer I found was Knowledge Base Article 818101, but the remedies work only for Money 2002 and 2003. Basically, certain 2004 components relating to .NET Passport have to be turned off. Can anyone advise me how to turn off .NET Passport in Money 2004. Otherwise, I have to install and uninstall Money 2004 each time I want to use the program. Thanks Are you using a Passport to authenticate access to your data file? File|Password Manager will enable you to disassociate your...

How to apply formatting to Excel with existing external connection ranges?
Hi, I used Advanced Find in MSCRM and exported the results in Dynamic Excel file. Now, I wish to format the list using Excel's 'Format as Table' in Home ribbon, but it warns me that an overlapping external data ranges occurred and it will be converted to a table and remove all external connections. How will avoid this? We wish to format the list and maintain the external connections from CRM. Please advise. ...

Please help me i'm an idiot
I have some templates that I've been using for a year now. When I used to type a persons name in a cell, and then go to type the same name in a cell a couple of rows down in the same column, it would automatically put the whole name in after I typed just one letter. Now it doesn't do that for me. What did I do to make this stop for me, and how can I get it to do it again? -- thuma1004 ------------------------------------------------------------------------ thuma1004's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14810 View this thread: http://www.exce...

Problem with entering data in Pocket PC version of Money 2004
Dear all, When I synchronize my Pocket PC with my PC, all the data I had entered on the Pocket PC version of the Money 2004 disappeared on both PC and Pocket PC. Can anybody tell me what 's that problem and how to avoid. Thank you very much. -Kien Bui ...

auto format chart colours
Is it possible to have a bar chart where all values greater than 100 are automatically coloured red, less than 100 are coloured green? thanks Yes, have a look at Jon's examples of conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy bobf wrote: > Is it possible to have a bar chart where all values > greater than 100 are automatically coloured red, less than > 100 are coloured green? > > thanks -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Excel 97/2000
Hi, Can someone please help me with a formula that I am having problems creating? In a folder I have 500+ 'randomly' named workbooks, plus one master workbook. The master workbook stays the same (it is the one I work with) whilst all the others will change daily. The changing workbooks all contain the same information types - that is each one is a set of contact details, with the file name being the contact name and the following example data: A1 - Address 1 A2 - Address 2 B1 - Telephone B2 - email address etc. On a daily basis, the master workbook is to collect all this data,...

Please help with Exchange POP3 Server
Dear all, I have a problem with mail exchage. I have an user account who have two mail boxs. one mail box is using exchange and another one is using POP3. The problem is the mailbox that using POP3 can not delivery mail to outside but can only delivery in the local. And it used to work normally before. I don't know what is wrong. So, anyone could you please help me with this? I look forward to hearing from you. Thanks in advance, Susan ...

Pop up Help
I use XP Prof. MS Outlook. Everytime I receive a email I get a pop up box that says "windows is preparing to install..." and almost as soon as I can read the box it disappears. I have no other errors or other problems but it is annoying. Does anyone know how to fix this? Thanks ...

Help Neede In How To Create Accounts receivable Or A/P Invoice
Hello, Could anyone let me know how a person can create a Great Plains A/R Invoice form scratch. Let's assume that a person has very small knowledge of Great Plains thus a very thorough and detailed description will be highly appreciated - the step by step description will be very helpful and appreciated. If also a detailed description of the same process concerning the creation of an A/P invoice is provided that would be even mcuh better. Also, could a trial version be donloaded or could even a tutorial from the Internet be found somwhere? Again, thanks in advance. You cann...

Help I like to convert a word doc into a pdf and insert it to Pub
I am trying to convert a word doc to a pdf and insert it into a Web site in Pub 2003. So our cusotmer could download it and print it. Thanks Mike You want to turn a .DOC into a .PDF so you can insert it into a Publisher-created website? I had to read that a couple of times to figure out what you were asking. (1) Publisher really has nothing to do with what you need to do. (2) You need a .PDF creating software program. I use Acrobat Pro but I'm fortunate in that it was purchased for my use. You either need Acrobat or you need one of the low-cost/free alternatives. -- JoAnn Paule...

IF statement 02-01-10
I HAVE 2 CELLS WITH DIFFERENT "IF" STATEMENTS Cell Q73 reads =IF(L73>=300000,"3000",IF(L73>=270000,"2700",IF(L73>=240000,"2400",IF(L73>=200000,"2000","0")))) Cell Q74 reads IF(L74>=200000,"3000",IF(L74>=170,000,"2700",IF(L74>=140000,"2400",IF(L74)>=100000,"2100","0")))) Cell F66 has the number 1 Cell F67 has the number 2 I am trying to write a formula that says IF F66 is a "1" use the formula in cell Q73 and if F66 is a "2" use the...

Creating a Formula to Format Column automatically? #6
What if I wanted to do more than three conditions -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 You can have 4 conditions. 1. The default 2,3,4 other conditions. More than that would require Code, most likely Event code. Example.... Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = I...

MS CRM
Is MS CRM suitable to be used as a Help Desk application? We are a company that handles approx 20 tech support calls a day but may grow to take 50 a day. If not, are there any other MS products that would be ideal for the Help Desk? CRM will work and do the job you want but if you only want it to handle 20 calls a day it may be expensive. How many users? "Matt" <FITZ@FRONTLINE-SOLUTIONS.COM> wrote in message news:147401c37312$bc10d090$a101280a@phx.gbl... > Is MS CRM suitable to be used as a Help Desk application? > We are a company that handles approx 20 tech supp...

how do I get a "read" receipt?
I know there is a way of getting an email acknowledgement that an email was received and read but I can't figure out how to do it. Help! Tools->Options->Email Options->Tracking options. Note, not all mail clients can send read receipts and many can decline to send them. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, caststeel asked: | I know there is a way of getting an email a...

2nd Exchange 2003 Server install failed during setup. PLEASE HELP! #2
I received the following error message in the middle of the second Exchange2003 setup.during Microsoft Exchange Messaging and Collaboration setup "Setup failed while Creating Active Directory objects for Microsoft Exchange Server-Level Objects (error 0x80072030: There is no such object on the server" . In event viewer i have: Event Type: Error Event Source: MSExchangeSetup Event Category: Microsoft Exchange Setup Event ID: 1002 Date: 15/09/2005 Time: 12.04.11 User: N/A Computer: MAILTEST Description: Exchange Server component Microsoft Exchange Messaging and Collaboration Serv...

FRX row format, MDA codes should allow me to use wild card charac.
FRX row format, MDA codes should allow me to use wild card characters, such as 11-1100-00,[Group.CODE???] 11-1100-00,[Group.CODE001]+11-1100-00,[Group.CODE002] -- Regards, Raja Raja, When using MDA Group and/or MDA Code within FRX you must explicitly enter the code IN FULL Use of wildcards is not supported, nor permitted. You will have to list each Code individually as you illustrate in your second line. Robert "Raja V" <rvijayarangan@hotmail.com> wrote in message news:84D34C61-13BC-42DF-B393-3F16E3E9C151@microsoft.com... > FRX row format, MDA codes should allow me to...