formatting a date in a text field

I need to format a date that is in a text field - ex. 20081108 needs to be 
11/08/2008 - how do I do this?

p.s. I cannot change the field from a text to a date - it deletes all the info
0
Utf
2/25/2008 7:09:02 PM
access 16762 articles. 3 followers. Follow

3 Replies
774 Views

Similar Articles

[PageSpeed] 27

First, to change the text field to a date format:

Format(DateSerial(Left([DateField],4),Mid([DateField],5,2),Right([DateField],2),"mm/dd/yyyy")

It would probably be best to change the field to date type for programming 
and reporting down the line.  To do this (first backup your data) then add a 
new field to your table (with a type of date).  Do an update query to 
populate the new field with an expression like shown above.  After the new 
field is populated, change the field names so that your new field is the one 
used in the program and the old one is a different name (if you still need it 
in your data) or is deleted after data is verified.

Hope this helps,
Jackie


"dchristo" wrote:

> I need to format a date that is in a text field - ex. 20081108 needs to be 
> 11/08/2008 - how do I do this?
> 
> p.s. I cannot change the field from a text to a date - it deletes all the info
0
Utf
2/25/2008 7:42:01 PM
On Mon, 25 Feb 2008 11:09:02 -0800, dchristo
<dchristo@discussions.microsoft.com> wrote:

>I need to format a date that is in a text field - ex. 20081108 needs to be 
>11/08/2008 - how do I do this?
>
>p.s. I cannot change the field from a text to a date - it deletes all the info

That's because, even though the date parser is pretty clever, it will not
recognize an 8-digit number as a date.

I'd suggest adding a Date/Time field to the table, and then running an update
query updating it to

CDate(Format([textdate], "@@@@-@@-@@"))

Check to be sure that you're getting valid and correct dates, and then use the
new date/time field instead of the text field.

If this will be a recurring import of some sort, you may need to make the same
change as part of the import process, or even use the CDate(()) expression as
a calculated field in a query.
-- 
             John W. Vinson [MVP]
0
John
2/25/2008 11:28:50 PM
To change the data type of the column to DateTime first run the following 
'update' query:

UPDATE YourTable
SET YourDate = FORMAT(YourDate,"0000-00-00");

which converts the string into the ISO standard date notation of yyyy-mm-dd.

Then run this one to change the column's data type to DateTime:

ALTER TABLE YourTable
ALTER COLUMN YourDate DATETIME;

You'll need to change the table and column names in the SQL to the real ones 
of course.  It goes without saying that the table should be backed up first 
before doing the above.

To simply format the existing text string you can use:

FORMAT(FORMAT(YourDate,"0000-00-00"),"mm/dd/yyyy")

Ken Sheridan
Stafford, England 

"dchristo" wrote:

> I need to format a date that is in a text field - ex. 20081108 needs to be 
> 11/08/2008 - how do I do this?
> 
> p.s. I cannot change the field from a text to a date - it deletes all the info

0
Utf
2/25/2008 11:30:00 PM
Reply:

Similar Artilces:

Need an Through/End date for the Historical Stock Status Report
Our customer wants to print a report for a prior month that shows the beginning quantity on hand for an item, all transactions for the month and the ending QTY balance. Put another way, the customer says "I want to know what inventory I started the month with, what happenned to it, and what I ended with." This could be accomplished by adding an end date option to the Historical Stock Status Report so we could run the As Of Date for the beginning of a prior month and the end date as the end of the prior month. ---------------- This post is a suggestion for Microsoft, and Micro...

Am I able to sort dates by month rather than year in excel?
I am using office 2003. I need a report that sorts my employees in order by the month they are hired. Excel always sorts by year. Just an example: If a date is in A1 and the rest of the column, then in B1 enter =MONTH(A1) and copy down Then sort by column B -- Gary's Student "Ruth k." wrote: > I am using office 2003. I need a report that sorts my employees in order by > the month they are hired. Excel always sorts by year. ...

trying to return data with only the latest date
when i run this i get data for the last day. i'm trying to get it to return only for the latest date. for example the instance name column contains all the drive letters. i would like it to return one row for each machinename and drive letter. tried adding distinct but didn't work with free_hd_space_cte as ( select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, b.countervalue, convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead from counterdetails a inner join counterdata b on a.counterid = b.counter...

fields
anyone know if you can put in a formula to automatically populate a field. I created the attributes in deployment manager.. added the fields. but the third field I want to be a product of the two? E.g. total or margin! Thanks for any help. Hi Joe, As far as I am aware there is no support for calculated fields. It would be possible to trigger an OnChange event, but in order to do so, you would need to link it to a picklist (combo box) on the form. This could then perform a calculation and assign it to the field that you want. Pretty ordinary solution, I know... but you work with wha...

How do I join 2 fields to limit criteria?
I am trying to set a criteria with an OR clause in between 2 fields. Let's call the 2 fields Field1 and Field2. I'm trying to do this example: Only show the records that have this criteria: Field1 = A, and Field2 = B,C,D,E,F,G These 2 fields contain the same type of data, and we need to limit the criteria together between the 2 fields?? Any ideas on how to do this when using excel to build a query in Microsoft query? Christen You could use an advanced filter. Assuming field1 and field2 refer to columns A and B, type this into an open cell (say M22) outside the data table: ...

Replacing cell with text
Hi, I want to know if it is possible to do the following: Imagine I have a legend in which I say which text corresponds to whic letters ex: John -> J Harry -> H Susan -> S and what I want to do is when entering J, H or S in a cell, it wil change to the corresponding text. Is there a way of doing it without having to run a macro ? Thanks in advance Pedr ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Go to Tools > AutoCorrect Options (XL2002) and insert new...

Excel Date Format of MMM/YY
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I enter a date in the format of MMM/YY it saves ok but if you then look in tools and calculator I have found that it creates a formula of 2004 and when you copy this over to a Windows based PC it displays incorrect dates. How do I turn off this apparent formula creation. I live in UK and have settings to UK Don't let the info in the Calculator mislead you. It's simply confirming that the workbook you're in is employing the 1904 Date System which is the default in Mac Excel. Dates in Mac Exce...

Converting Text Values to Dates
Is there a way to convert a column of text, date values (entered as 81096, for example) to valid date format, 8/10/96? I could not get the DATEVALUE function to do this. Hi! Select the range of cells in question. Goto Data>Text to Columns Click NEXT twice Select Column Data Format: DATE. Then choose the format from the drop down Finish Biff "Frank Winston" <FrankWinston@discussions.microsoft.com> wrote in message news:7CAF0A61-E8A7-4D18-B035-0D475E9A858C@microsoft.com... > Is there a way to convert a column of text, date values (entered as 81096, > for example...

Outlook 2003 HTML formatting problem
After upgrading clients to Outlook 2003 we've started to experience a strange problem with emails formatted in HTML. The scenario goes like this. 1. A local user creates an HTML formatted email and sends it to an external recipient. 2. The external recipient replies to the original message. 3. Originator replies to the reply 4. The external recipient receives the HTML formatted email with portions of the text grossly enlarged. 5. Upon review, it would appear that the external HTML client dropped a (.) changing the font size from 10.0 to 100 causing the enlarged text. Editing the HT...

change color in many text box
I have a form which takes information from a query. The information is orizontally aranged in the many text boxes . I would like to know how can i change the background color of the text box on the row by just doubleclicking on that particular row. Give me some suggestions please! ANY IDEEA, "florin" <florin_serban@hotmail.com> wrote in message news:ercv$ytcIHA.5164@TK2MSFTNGP03.phx.gbl... >I have a form which takes information from a query. > The information is orizontally aranged in the many text boxes . > I would like to know how can i change the background colo...

How to add custom field to Task Form in Outlook2k?
I have created custom fields for task and want to add them to the form that pops up when I create a new task. I have added my custom fields to the Simple View & others where task are listed... But I want to be able to input custom data when I create a task... And I want to be able to modify that custom data later if necessary. How can I make my Custom Task Fields appear in the standard Task Form ? thanks for any help. tmb Start by going to http://www.slipstick.com/dev/forms.htm to see about Outlook forms design. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Abso...

formatted values from single equation
I would really like to have a Excel spreadsheet with two worksheets, the first sheet has raw data (16 across, 100's down) and what I would like to do on the second sheet is add to the first row, 16 equations (lets say for now y=x). then underneath this the formatted data where x is cell A1 on the first worksheet and y is on the second worksheet. Obviously you would just normally in each cell on worksheet 2 type the equation in (='worksheet1'!A1), but I would like to show people the equation being used. Is this possible, does this message make any sense?? Nice -- mr_nice! ---...

Formating in a Report, the highest 3 records
How do i can format the hightes 3 records on an Access Report? Unfortunately the Access doesn't have the Excel have the function "Large"... Can you help me? Look in Access Help for "TOP". HTH -- -Larry- -- "Luis Marques" <Luis Marques@discussions.microsoft.com> wrote in message news:F467B234-EA95-4F79-91B0-EDC12E61FD00@microsoft.com... > How do i can format the hightes 3 records on an Access Report? > > Unfortunately the Access doesn't have the Excel have the function "Large"... > > Can you help me? Try writing your ...

OL2000 Text disappears for certain recipients
Hello, There's been a problem the last few days with the Outlook 2000 in the office I work. Certain users will reply to emails, but only most of the recipients will be to read the reply. Some just see the original message, with the reply text at the top completely gone. This is kind of nerve wrecking, since it's intermittent and not predictable. None of the Outlooks are formatted with rich text, and we use an ISP's email service. Any thoughts/suggestions would be greatly appreciated! Oh, and the OS's are either Win 2000 or XP. Thanks! ...

User form Default value in text box
Hi all. Quick question. I have a user form in excel which has a text box which i want to have the value defaulted to the current date. I've looked at several posting on this site but haven't gotten it to work yet. Any ideas on how to set the text box with the current date as a default value? Any help or direction is appreciated. Thanks. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201006/1 Hi, Try this way Private Sub UserForm_Activate() TextBox1.Text = Date End Sub -- Mike When competing hypotheses are oth...

XSLT Plain Text is Indented
Hi All, I'm having some trouble getting my XSLT to transform correctly. VS.NET addeds Indentations: <?xml version="1.0" encoding="utf-8" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="text" indent="no" /> <xsl:param name="Name"/> <xsl:template match="/"> Hello <xsl:value-of select="$Name" />, We have received your e-mail, and your question is in the process of being routed to the appropriat...

How stop duplicate fields when creating a Report in Access?
no metter what method I use to create the report - it throws double fields into the report. You manually delete one of each and proceed and it continues to add double fields. We did not have this issue in 2003 version. WHat is the problem and how do we fix it? Are you talking about the record source (a query) for the report listing fields twice? If so, check the underlying query and see if it has an asterisk (return all fields) in SELECT clause of the query. It is a property of queries in design view (Output all fields). Set this to No to eliminate the fields showing ...

File Formate
I have five Excel files which contain the records of my previous 4 years Business matters. Suddenly yesterday when I opned one of my files I received the following messege. --------------------------------------- The file is not in recognizable formate. if you know the file is from another program which is incompatible with Microsoft Excel, click Cancel, then open the file in its original application. If you want to open the file later in Microsoft Excel, save it in a format that is compatible, such as text format. If you suspect the file is damaged, click help for more information about...

Different formats within the same cell
Hello, i want to enter a word in a cell but only have one of the letter fomatted bold, eg - "wo*r*d" Is this possible -- Message posted from http://www.ExcelForum.com Hi, Yes. Enter your word into the cell, go to the formulae bar, highlight th letter you want to have in bold and then click on the bold icon. This will format just that letter to bold -- Message posted from http://www.ExcelForum.com sure. Just edit the word>highlight the letter(s) and change the font,bold,color etc. will NOT work withing a formula. -- Don Guillett SalesAid Software donaldb@281.com "...

Number formats and fields
Hi All! I have a list of equations in a word document that I need to add a reference (the first equation in the document will be equation A, the second will be equation B, etc). So I need a numbering field. However, when I get to equation 28 I want the format to be AB, then 29 to be AC, not BB, CC like the default A, B, C… numbering gives you. However, I will be adding other equations randomly throughout the document and referring to the equation reference in text paragraphs, so I would like them to auto update when I add another equation in the middle of the document. Doe...

Import Text Wizard
I'm trying to open a .txt file from Windows that starts the Import Text Wizard automatically. All I've managed so far is to actually open it, yet if you open a text file in Excel it starts the wizard itself. Gary ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ I believe that the Text Import Wizard runs automatically from the user interface. You can run it from Data, Text to Columns after the file is opened. -- Jim Rech Excel MVP ...

Formatting a date in client report
How do I format a date field in a client side Microsoft report (rdlc)? I'm using VS2008, 9.0.30729.1 sp Windows forms project. Unformatted, the dates in my report are displayed like this: 11/19/09 00:00:00 I want to display just the date, not the zeroes. If I enter an expression in the value property for the report textbox like this: =Format(Fields!StartDate.Value, "D") (as shown in http://msdn.microsoft.com/en-us/library/ms251668.aspx) the report will display "D" instead of the formatted date. The same thing happens with other format string...

Task Due Date
Is there anyway to preset the task to have "today" as the due date by default? -- Guy Lapierre Forefront Business Solutions http://www.forefrontbusinesssolutions.com Not that I am aware of, no. --� 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, Guy Lapierre asked: | Is there anyway to preset the task to have "today" as the due date by | default? ...

Conditional Formatting #84
I would like to use conditional formatting in a number of cells. The condition I would like to highlight would be if the cell contained text. ie. 1Y Can anyone help me with this as I am fairly new... Thanks!! Mike 1. Select your range. 2. Go to Format > Conditional Formatting. 3. Select "Formula Is" and insert the following: =COUNTIF(INDIRECT("rc",0),"*1Y*") 4. Press the Format radio button and format as desired. HTH Jason Atlanta, GA >-----Original Message----- >I would like to use conditional formatting in a number of >cells. The co...

Set date field year to 2005
Hi there, I have a spreadsheet of dates, EG: '10/09/1983', '03/08/1985'. I need to change these all to be in the year 2005, so '10/09/1983' becomes '10/09/2005'. I assumed it would be a case of just doing a custom format DD MMMM 2005 but this doesn't work. Is there an easy worksheet function to do this or will I need to write a macro? Cheers, Chris If it's a one timer you can use formulas =DATE(2005,MONTH(A1),Day(A1)) will take the date in A1 and change the year to 2005 then you can just copy and paste special as values over the old dates, finally...