date is in 20010129 format, how can I get it in m/d/y format

-- 
jsanders
0
jsanders (2)
1/12/2005 7:29:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
574 Views

Similar Articles

[PageSpeed] 54

Data>text to column, go to step three and select date and YMD

and for future posts it's considered rude just using the subject line


Regards,

Peo Sjoblom
0
PeoSjoblom (789)
1/12/2005 7:35:02 PM
With the value 20010129 in cell A1, enter this formula in B1:
=DATEVALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4))

and the format the cell as a date.  The various functions parse the
20010129 string into its components, and the DATEVALUE converts that
string to a datevalue that Excel can recognize.

0
CycleZen (674)
1/12/2005 7:36:47 PM
Reply:

Similar Artilces:

Users can give themselves any roles
I've got a custom role that has read only (user only) access to user settings. This user role does not have assign role privilege. However, If I go to personalize workplace on the general tab I can click on "view your user profile here" and once on the user screen I can give myself any role I want, which is obviously not good at all Is this a CRM problem? I would remove personalize workplace from the sitemap if I knew how, but I already posted and nobody seemed to know Any ideas? TIA When I click on "view your user profile here" as described in my previous pos...

Budget question...can't find "Special Categories"
Question for the Budget section. I can't find the "special" categories that are listed on my checking account. In my checking account to pay a credit card bill, I select "Credit Card Payment: Card Name" from the special section. This links the two accounts so I don't have to make 2 entries...works fine. BUT in the budget section, I want to account for the same credit card payment. I can't select the same "Credit Card Payment: Card Name" This causes a problem as the Budget now won't recognize that the payment was made since it is lo...

can't choose just 1 message????
I try to choose just one message but it won't let me. It choose's like 10-20 messages at a time. does anyone know what I can do to fix it or what the problem is in general. Please help. Make sure your SHIFT key isn't stuck. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "hockeyjunkie" <anonymous@discussions.microsoft.com> wrote in message news:3ead01c47f4f$2382dfe0$a401280a@phx.gbl... >I try to choose just one message but it won...

=IF(D13="PAID","YES","NO") Can I change fonts colour
How do I Change the colour Of "YES" To Blue and "NO" to red Look up Conditional Formatting in help, it does that sort of thing. -- HTH RP (remove nothere from the email address if mailing direct) "Kev" <Kev@discussions.microsoft.com> wrote in message news:805AB2EB-2A35-4F3F-BB8F-D04239904000@microsoft.com... > How do I Change the colour Of "YES" To Blue and "NO" to red Conditional Format the cell. Formula is =d13="YES" then under format change font color. Then add another condition for your no. Here's the li...

Convert '12-03 to 12-03 date
A co-worker created a spreadsheet with dates keyed in a column as text (e.g., '12-03 represents December 2003). I need to convert a very long column of these "dates" to actual dates in the format MM-YY. Everything I try converts it to the proper month, but year 05 (2005). Your help is appreciated. -- Mytpoet67 If your first entry is in A2, then =DATE("20"&RIGHT(A2,2),LEFT(A2,FIND("-",A2)-1),1) will turn that into a date. Autofill through the entire column. HTH. --Bruce "Mytpoet67" wrote: > A co-worker created a spreadsheet with dat...

Can't see any cells in Excell
I have a worksheet that I have been using for monthly reports. Every thing was fine last month but today when I went to enter my data I could not view my date when I zoomed in 100% all I got was a blank screen. I went to print and it printed fine except for any thing that was in bold print didnt print. The worksheet is saved on a floppy I tried to open it on a different computer I could do every thing with no problem. So I uninstalled and reinstalled office 2000 now when I go to open up Excel program from the harddrive its blank... you can not see any cells its all gray. Never, ...

how to get my window position in MFC?
my question : how to get my window position in MFC? thnks GetWindowRect ...

Can't install full version MS Money 2007 Deluxe
I downloaded MS Money 2007 Deluxe Trial, a few weeks ago. It installed with no problem and I have been using it since. Today, I bought, online, the full version of the above software. I can't get it to install. When I try, I get the following message: "Microsoft Money 2007 Deluxe Setup has stopped working. Windows can check online for a solution to the problem." Checking online does nothing. Should I completely uninstall the "trial" version? I don't want to lose my data. I have it backed up. Thanks, WLW In microsoft.public.money, wlwallick wrote: >...

Check dates
I have 2 dates, like this: 3/10/2010 and I need to make sure the 2nd one is a later date than the first one. How can I do this? (sorry if this is double posted, pc crashed) "Southern at Heart" <SouthernatHeart@discussions.microsoft.com> wrote in message news:506B487E-8F47-4CA9-98AA-E2900BC84591@microsoft.com... >I have 2 dates, like this: > 3/10/2010 > > and I need to make sure the 2nd one is a later date than the first one. > How > can I do this? > (sorry if this is double posted, pc crashed) Assuming your dates are in variables Date...

Automatically formatting and printing messages
I have an html form that sends an Email to my Outlook account. In the Email is pertinent information that I would like to process in the following way: -Format the body of the Email using a Word Macro/Auto-Format template -Automatically Print the message -Move the message to a folder entitled "Processed" I am thinking that I can use an Outlook rule that would touch-off an Outlook macro to select the body of the Email. The Outlook macro would then call MS Word, passing the name of a Word macro as a parameter which Word would execute on the contents of the clipboard and then close ...

Cannot get my Building Block template to show...
I have added my own template (MyTemplate.dotx) with its own specific Building Blocks into the folder: "c:\Documents and Settings\<UserName>\Application Data\Microsoft\Document Building Blocks\1033\" However, when looking at the Building Block Organizer I only see those elements that exist in Normal.dotx and Building Blocks.dotx. How can I get to see my elements from MyTemplate.dotx? I understand that it should list all Building Blocks from templates included in that folder - meaning that my elements should supplement those already in the list? Thanks Robi...

Excel Cell Formats
How do I format cells in the Canadian postal code format? What's to format? Simply treat it as text. -- Regards, Fred Please reply to newsgroup, not e-mail "Ron" <Ron@discussions.microsoft.com> wrote in message news:6CE059EE-8938-4915-B364-2D0D1F1D9449@microsoft.com... > How do I format cells in the Canadian postal code format? By typing the space. -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm &q...

Getting a "Not a Valid Win32 Application" when i try to open doc.
I am trying to open a publisher file that was sent to me. I have Publisher 95. I am getting the error message "......is not a valid win32 application" Can anyone tell me how to fix this? Publisher 95 will only open Publisher 1.0 and 2.0. Publisher 95 is a 16 bit program. Maybe your sender can convert the document to PDF. Or send it to me and I will convert it to PDF for you. I can open the 97 document but I won't be able to convert it to 95 for you. Remove "my" to reply... -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews...

Can't connect to server question
I have Outlook 2000 and recently upgraded from Win 2000 to XP SP2. I have all the settings the same as before but when I send/receive the pop-up comes up and it says that everything went fine. Problem is, nothing happened. No messages were downloaded and no messages left the outbox. If there were an error message I might know where to look to fix things, but it says everything is fine. I have also turned off XP's firewall and tried it that way, but same problem. Can anyone help? Thanks, Bob One more thing, looking closely it says "0 of 0 tasks performed successfully"...

Can both office 2007 and office 2003 operate at the same time
I just want to use both office 2007 and office 2003 while I am getting use to office 2007. Is it possible to run both on one computer? Is it possible? Yes. Is it a good idea? No. (And Microsoft recommends against it.) You are asking for configuration problems now and in the future. Unless you are interested in dual-booting or a virtual environment - and honestly, why bother - the wisest course of action is to take advantage of the enormous amount of instructional material available on the web. The 'old fashioned' method - a book - also works well. Besides, everyone...

outlook date incorrect
Has anyone experienced any problems with the received date being incorrect in Outlook, the date seems to come as 04/09/03, in that all dates arrive as this. This only seems to happen with Outlook and not Outlook Express The time seems to be okay. Thanks in advance Trev. ...

can text be inverted by 180deg
Hi, Is it possible to invert text by 180 deg. Under Format - cells you onl have the option of 90 deg -- trye ----------------------------------------------------------------------- tryer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1654 View this thread: http://www.excelforum.com/showthread.php?threadid=39979 from my archives sometime back i used this formula =OFFSET($B$1,MATCH($B$16,B1:B16,0)-1,0) my original data is in B1 to B16 I copied this to any cell and copy down try this. may be easier solutions available tryer <tryer.1ugkia_1125205502.54...

Spreadsheet End Date
Can I write something into a spreadsheet so that it will only work for a set period of time? TIA Michele Try this in the Thisworkbook module It will only work if the user Enable the macro's Private Sub Workbook_Open() If Date > DateSerial(2005, 1, 22) Then ThisWorkbook.Close False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "mjh" <mjh@discussions.microsoft.com> wrote in message news:DA3D73B7-2AAE-4B76-82ED-C117CF996166@microsoft.com... > Can I write something into a spreadsheet so that it will only work for a set > period of time? > >...

My 3-D toolbar is not working.
When I select on a 3-D object in a worksheet created by another user, my 3-D toolbar becomes active. However, when I create a text box, and select it, my 3-D toolbar doesn't become active. Can someone help? ...

Using IMAP w/ 2000 Changes Date/Time
I'm hoping someone can assit. I'm using Outlook 2000 with a IMAP server from my hosting provider. I can see messages in my IMAP inbox time stamped correctly but when I "select the message" displaying it in teh preview pane the date and time jumps forward 6+ hours. I'm alrady at a SP3 level and don't know what to do. Please help. Dean T. Rose Dallas, TX ...

I need a formula to sum column b if column a is between two dates #2
I have an excel spreadsheet with employees time off. I need a formula that will add column b if column a is betwee two dates. For example: if column a is between 9/22/04 and 9/21/05 then add column b. I have tried all different formluas but can't get this to work. =SUMPRODUCT(--(A2:A100>DATEVALUE("9/22/04")),--(A2:A100<DATEVALUE("9/21/05")),B2:B100) check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html for details on the sumproduct function "Pam" wrote: > I have an excel spreadsheet with employees time off. I need a formula that &...

How computer monitors can or cannot kill eyes?
HP ZR22W and in general HP - just love it. This is a new model and it's IPS panel (made by LG for HP, though)., widescreen or vertical (rotates) and under $300. I know Windows Newsgroup is not a perfect place to post this "opinion", but I just feels it needs ot be shared: I spend long hours in front of various monitors due to design work & ALL KINDS of work (for which 2 entrepreneurs didn't even pay for because I was naive to work on enthusiasm alone) Anyway, so the monitor choice is more cirtical to me than ANYTHING else. Nobody wants cancer or vision ...

How can Outlook retrieve mail if not running?
My Outlook 2002 keeps telling me I have new mail even if it has not been started. How can it do this? Beemer Something is keeping outlook.exe running in the background. Perhaps a PDA? Fax software? Beemer wrote: > My Outlook 2002 keeps telling me I have new mail even if it has not > been started. How can it do this? > > Beemer In article <ut#hIfNvDHA.1512@TK2MSFTNGP10.phx.gbl>, lanwench@heybuddy.donotsendme.unsolicitedmail.atyahoo.com says... > Something is keeping outlook.exe running in the background. Perhaps a PDA? > Fax software? > > Beemer wrote: &...

how do i use multiple conditional formats in one cell?
I need to make difrent "words" in one cell make number values in a difrent cell tyson CF will allow up to 4 formats per cell, including the default format. However, CF will not place number values in cells. Can format only as in Fonts, Borders and Patterns. What would you like to have done? Perhaps there is another method. A worksheet function such as one of the LOOKUP functions or MATCH. Gord Dibben Excel MVP On Fri, 21 Jan 2005 14:55:03 -0800, "tysonstone" <tysonstone@discussions.microsoft.com> wrote: >I need to make difrent "words" in one ...

Getting Windows Installer when trying to open Excel files
I have suddenly begun experiencing a problem when trying to open Excel files or the application itself. I get Windows Installer saying it's trying to load Office XP Small Business, and asking for the CD. I don't have the CD, as my PC came loaded with Office already on it. When I subsequently click "Cancel", I get an "Error 1706... setup cannot find the required files". I have not done anything new to my PC, and suddenly this problem. What gives? Any help would be appreciated. Thanks in advance, AMC Contact the company who sold you the PC, I don't thin...