Getting date stored as text into real date?

A database query program outputs everything as a text string.  One of
the fields is a date, formatted as yyyymmdd.  Is there a worksheet
function that will change this to an Excel-recognized date?  Or a
macro?  The error checking doesn't flag this.

Ed
0
prof_ofwhat (194)
12/17/2007 1:12:09 PM
excel 39879 articles. 2 followers. Follow

3 Replies
345 Views

Similar Articles

[PageSpeed] 47

With your text date in A1, try this in B1:

=DATE(LEFT(A1,4),MID(A1,5,2),(RIGHT(A1,2))

Hope this helps.

Pete

On Dec 17, 1:12 pm, Ed from AZ <prof_ofw...@yahoo.com> wrote:
> A database query program outputs everything as a text string.  One of
> the fields is a date, formatted as yyyymmdd.  Is there a worksheet
> function that will change this to an Excel-recognized date?  Or a
> macro?  The error checking doesn't flag this.
>
> Ed

0
pashurst (2576)
12/17/2007 2:36:37 PM
Don't need a macro - Select your dates and choose Data/Text to Columns. 
Click Next, Next, then choose yymmdd from the Date dropdown. Click 
Finish. Format the dates as desired.

In article 
<250cc4a4-a397-4f3b-b19b-00337981a990@e6g2000prf.googlegroups.com>,
 Ed from AZ <prof_ofwhat@yahoo.com> wrote:

> A database query program outputs everything as a text string.  One of
> the fields is a date, formatted as yyyymmdd.  Is there a worksheet
> function that will change this to an Excel-recognized date?  Or a
> macro?  The error checking doesn't flag this.
> 
> Ed
0
jemcgimpsey (6723)
12/17/2007 2:45:08 PM
That worked great!!

Thanks!!
Ed


On Dec 17, 7:45 am, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> Don't need a macro - Select your dates and choose Data/Text to Columns.
> Click Next, Next, then choose yymmdd from the Date dropdown. Click
> Finish. Format the dates as desired.
>
> In article
> <250cc4a4-a397-4f3b-b19b-00337981a...@e6g2000prf.googlegroups.com>,
>  Ed from AZ <prof_ofw...@yahoo.com> wrote:
>
>
>
> > A database query program outputs everything as a text string.  One of
> > the fields is a date, formatted as yyyymmdd.  Is there a worksheet
> > function that will change this to an Excel-recognized date?  Or a
> > macro?  The error checking doesn't flag this.
>
> > Ed- Hide quoted text -
>
> - Show quoted text -

0
prof_ofwhat (194)
12/17/2007 5:35:06 PM
Reply:

Similar Artilces:

Getting a subform control to requery
Main Form : F_BU Subform1: F_BU_Cat2 SubForm2: F_BU_Cat3 When I click on one of the records in subform1 (field BillCat) the OnClick event uses the value in the field as a criteria in a query that is used in the combox (cbxCat3) in Subform 2. Here is the code I was trying to use: Me.Parent!F_BU_Cat3.Form!cbxCat3.Requery It was working for a while but now not when I click on the "BillCat" field in Subform 1 I get: Method: Form" of Object ' _Subform' Failed. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-form...

Picts in text
When I receive picture or other e-mail from a friend it occasionally arrives as a "txt" file. Not all the time... Occasionally also, if he sends me a series of, say 5 or 6 picts I have just a box with the red x, but as I sroll farther down the page the pictures are present. thanks in advance, jem what email client does he use? the problem could be on his end or yours. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginn...

We are getting stat=Deferred: Connection reset errors .....
Does anyone know how to fix this issue? I am getting a lot of emails BUT an Exchange admin from another company that sends SMTP email to is saying that he sees a lot of stat=Deferred: Connection reset error messages in his log file. very very weird. Has anyone seen this issue? He can telnet to the server fine etc... plus I am getting email from his company but it is deferring aa ton though. Thanks What version of Exchange and SP level Oliver Exchange 5.5 SP 4 is our IMS. "Oliver Moazzezi" wrote: > What version of Exchange and SP level > > Oliver > >...

MSExchangeIS Public Store Errors 1110
We are running Exchange Verson 6.0 (Build 6249.4; Service Pack 3 We are getting the following error 39 times every hour Event Type: Erro Event Source: MSExchangeIS Public Stor Event Category: General Event ID: 111 Date: 2/20/200 Time: 6:21:36 P User: N/ Computer: MAI Description Error 0x0 occurred while writing per-user information for <user@outdomain.com> on database "Staff\Public Folder Store (MAIL)". For more information, click http://www.microsoft.com/contentredirect.asp. This error is supposed to be solved by the service pack (Q312966). We applied...

can I get "step by step" on color seperating for commercial printi
This is kind of an extension on my previous message but I feel it would help a lot more. Can someone give me a quick step by step on making a document with text and photo into a press quality color seperated piece using PMS colors and process black? Or maybe point me to a site that can give step by step on that stuff? ...

multiple text edit
Hi all, I'm a beginner when it comes to Visio. I’m using Visio 2007 and I’m wondering if the following is possible. What I want to be able to do is to change the text in a number of textboxes by just editing one of them - that is, when I change the text in one textbox, the three other textboxes will display what I’ve just written automatically. Can this be done? Thanks in advance. the answer is "kinda". You can coordinate text between shapes so that changing a specific shape will be mirrored to others. It's a one to many, you don't get the option of changing anyo...

Where can we get xml scheme for tuning setting of WM2003/WM5.0 PDA
Hi all, We are working upon setting up a lot of WM2003/WM5.0 empowered mobile devices according with company standards. As far as I know these settings are tuned through xml file. Then this xml file has to be deployed deployed/executed onto mobile device. My question is where xml scheme for tuning such setting is published so we may assign necessary properties referencing it? Thank you in advance. ...

How to get Global Notification of browser request...
Hye, I am trying to get notification of any browser request... (not only that of the local server.. so filters will not be the right way to solve problem) So, whenever any request like (http://www.yahoo.com/) is made in IE, I want my DLL routine or my EXE should be called.. So, how to do that, any suggestion will be highly appreciated... -- Jigar Mehta jbmehtain@yahoo.co.in the program you need is called Browser Helper Objects http://msdn.microsoft.com/library/en-us/dnwebgen/html/bho.asp -- Command what is yours Conquer what is not Microsoft Most Valuable Professional [VC++] h...

how to produce a csv file from a unicode-saved text file
Friends, I have an excel spreadsheet (version 2000) that has Japanese characters. I would like to save it as unicode text file so that I can FTP it to my unix box for loading into an Oracle database. When I save the file as unicode text, it properly preserves the characters, but using whitespace as the delimiters. I would like to change this to a csv, but when I save as the file as CSV, the Japanese characters turn into set of ?? symbols. Is there any way around to create the unicode text file with delimiters other than whitespace? Thanks for any ideas. - Sal ...

Emails getting stuck in Outbox
Hi I am sending a number of emails (around 600) using the vb.net code below. The problem is that the emails end up in the OL2007 Outbox with a clock icon and do not go out. If I open one of the email items and click Send then that item goes out but this technique is too cumbersome for 600 or so emails. What is the problem and how can I fix it? Many Thanks Regards Dim objOutlook As Object = CreateObject("Outlook.Application") Dim NS As Object = objOutlook.GetNamespace("MAPI") Dim objOutlookMsg As Object Dim BodyText As String ...

problem while starting "Microsoft Exchange Information Store" service
Hi, I am facing some problem with starting "Microsoft Exchange Information Store" service. Did a fresh exchange server installation and after reboot this service was not running. I tried running this service manually but getting error as "Could not start Microsoft Exchange Information Store service on Local Computer Error 1053: The service did not respond to the start or control request in a timely fashion" In the event Application logs i am getting some warnings and error related to 1. The MAD Monitoring thread was unable to read its configuration from the DS, error ...

Excel 2000: Regional date problem
We'e just upgraded from Excel 97 to 2000 and are having problems because dates in spreadsheets are showing in US (mm/dd/yy) format rather than in European (dd/mm/yy). This si true whether I use a standard date format or make a custom format. I've checked the regional settings in Windows 2000, and they are correct. I thought Excel picked up the default from here. Any other ideas where I might look? Thanks in advance Alan I wasn't able to duplicate your problem, Alan. I switched to UK settings through Regional Settings. Then I opened Excel 97 and entered =NOW() in two ce...

Date format problem #2
Dear All, Please suggest me the solution of my problem, as i m handling data for 60,000 in no.Some body in data entry has enter the date in a cell in gernal format e.g 20.03.2003 tht is 20th march 2003, i wnt to convert in the date fomat,like 3/20/03.. i tried ..but i failed to tht please suggest some alternative solution for tht..becoz it is not possible to do it mannually waiting for the response Thanks NISHANT Nishant Try using Data/Text to Columns. and assign the information as a date. Andy. "Nishant" <nishant.khare@wipro.com> wrote in message news:090201c37ce0$1b...

How Do I specify where XML data gets imported into Excel
I have XML data that I would like to import up as fllows in a spreasheet: Cell A2: Job Number Cell A3: Customer Number A4 through A29 should recive the <AMOUNT> data B4 through B29 should receive the <QUANTITY> data etc etc Sometimes all the fields will be filled, sometimes not. Any help would be greatly appreciated. Regards, Diane Hi Diane! You should see therre: microsoft.public.xml Starwing ...

Dates as text
using a formula, I am combining a column heading and the date in that row, so that the cell will read "Column Heading Date", but the date is showing as a number rather than a date. I want the cell to contain both txt (column heading), and a date. How do I format it to do so? assuming your date is in cell A1: ="heading text "&DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1) -- hope to help, cm "lightbulb" wrote: > using a formula, I am combining a column heading and the date in that row, so > that the ce...

Can not see my text when I try to edit the text box.
I have Visio for Enterprise Architects SP-2. I tried to edit the text box created by the previous visio versions using pointer or A mode. When I double-click the text or select the text, they are blue. When I enter into the text, I can not see the text that I suppose to edit. I have the same software on the laptop where I do not have the problem. How can I set the option so that I could see the text that I need to edit? Usually the background color contrasts with the text color to make it visible, but in some cases the background color and the text color match or are very similar. I...

date reference
hi all, can a single cell accomodate TWO different DATES, and yet refer to them in formulas? i tried having in A1 [from 07/04/01 to 10/15/03] and by using right(left(a1,13),8) to extract data starting from 07/04/01, and right(a1,8) also to refer to end of period selected at 10/15/03. always i get a zero anwser or error. thanks for any help Hi what is the exact value in cell A1 and what are your exact formulas? -- Regards Frank Kabel Frankfurt, Germany "excelFan" <excelFan@discussions.microsoft.com> schrieb im Newsbeitrag news:3D423A44-6973-4A31-89B8-38321957...

How to change the text of a menu item dynamically?
Hi to all, I created a dialog-based project with Visual C++ 6.0 and added to the dialog a menu. This menu is the following: File Help Sound On About Exit Everything is working fine so far. What I want to do, is change the text of the menu item "Sound On" to "Sound Off" every time the user clicks on it. I am using the CCmdUI* pCmdUI of the ON_UPDATE_COMMAND_UI handler to do it as follows: void CMyDlg::OnUpdateFileSound(CCmdUI* pCmdUI) { TRACE("OnUpdate\n"); pCmdUI->SetText(m_bSound ? "Sound On" : "Sound Of...

Modified date
Please can anyone tell me how I can place in a cell on a sheet the date the workbook was last modified? Thanks Shane. Create a UDF '----------------------------------------------------------------- Function DocProps(prop As String) '----------------------------------------------------------------- Application.Volatile On Error GoTo err_value DocProps = ActiveWorkbook.BuiltinDocumentProperties _ (prop) Exit Function err_value: DocProps = CVErr(xlErrValue) End Function and enter in a cell such as =DocProps ("last author") or =DocProps ("last sa...

HELP!! Repeating Text over multiple Pages
I created a template containing a title, picture, map, and text box. The text box in particular contains information pertaining to a specific location. I created a 131 individual files using that template and everything came out looking great (thanks Microsoft). I had to create 131 individuals files because we needed to have it backed up for our server at work. The problem I am having now is when I attempted to combine each of 131 files by copying and pasting them into 1 publisher file containing 131 pages. The title, picture and map all preserve their own information. HOWEVER, the in...

email cannot store in exchange 2003 server
i'm using exchange 2003 server and outlook 2003 (pop3) as client computer, i tick the box to keep email in server when use outlook 2003 to receive email, but I cannot find any mail in server afterwards, anybody know why? thanks!!! JY Try this: Create and configure the "Message Size Limit" registry entry, follow these steps: 1. Click Start, click Run, type regedit, and then click OK. 2. Locate and then click the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSExchangeWEB\OWA 3. Right-click OWA, click New, and then click DWORD Value. 4...

date/time stamp on user exit
I have a database that logs when a form (RPA) comes into our office. The RPA will go through a few different hands and will have different information entered into the database from each person. How do I get a log of when information is entered and by who? I am a beginner so a detailed explanation would be helpful. Thank you! On Wed, 27 Feb 2008 14:26:00 -0800, Kim <Kim@discussions.microsoft.com> wrote: >I have a database that logs when a form (RPA) comes into our office. The RPA >will go through a few different hands and will have different information >entered into the...

import dates to calender
I would like to import dates from a .csv file to a calender, preferably Publisher, how? ...

How do you send data to the same row of a text file everytime?
All, How do I output to the same position in a Text file everytime? I want to output data for a given Date so I'd like to put it in the same row for that Date everytime, and so overwrite the existing data in that row? I use the following command: Write #10, dateline, variable1, variable2, variable3 'where dateline is today's date in excel date code. I drop into the file at the row corresponding to last week's date e.g. row 40010. Write #10, dateline+1, variable1, variable2, variable3 'where dateline +1 is tomorrow's date in excel date code. I drop...

Preventing auto date formatting
Excel keeps on insisting on formatting my data as date without asking! This is happening in two specific cases: 1) (which I have asked about here previously) When I paste data from the web into Excel that contains data such as 1-3, Excel insists on re-formatting it as 3-Jan. It doesn't matter if I preformat the cells to Text-- the reformatting still occurs. 2) When I have existing data such as 3/4 V, and I use the Replace command to strip the V, Excel reformats the remainder as 4-Mar. Again, the cells in question are formatted as Text before using the Replace command. Does anyone know ...