Converting text to dates

Hi -

I'm trying to convert a simple value of 7152004 to a date, and can't seem to 
figure it out. When I format cells to date, I get a long #### response, and 
I'm not getting that b/c of the column size. I've tried to do DATEVALUE on 
the cell, but it gives a VALUE! error. Any suggestions? Thanks!
0
Jack4963 (284)
4/25/2005 6:20:04 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
444 Views

Similar Articles

[PageSpeed] 35

In an adjacent cell, use

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

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Jack" <Jack@discussions.microsoft.com> wrote in message
news:12808E82-5C8B-4A5C-A1B2-E4FE2C9C2F9F@microsoft.com...
> Hi -
>
> I'm trying to convert a simple value of 7152004 to a date, and can't seem
to
> figure it out. When I format cells to date, I get a long #### response,
and
> I'm not getting that b/c of the column size. I've tried to do DATEVALUE on
> the cell, but it gives a VALUE! error. Any suggestions? Thanks!


0
bob.phillips1 (6510)
4/25/2005 6:30:24 PM
That worked! Thanks!

"Bob Phillips" wrote:

> In an adjacent cell, use
> 
> =DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "Jack" <Jack@discussions.microsoft.com> wrote in message
> news:12808E82-5C8B-4A5C-A1B2-E4FE2C9C2F9F@microsoft.com...
> > Hi -
> >
> > I'm trying to convert a simple value of 7152004 to a date, and can't seem
> to
> > figure it out. When I format cells to date, I get a long #### response,
> and
> > I'm not getting that b/c of the column size. I've tried to do DATEVALUE on
> > the cell, but it gives a VALUE! error. Any suggestions? Thanks!
> 
> 
> 
0
Jack4963 (284)
4/25/2005 6:36:09 PM
Reply:

Similar Artilces:

Expiry date for current version
I have been suing And which version is that? The general answer can be found in Help | About. "srn" <srinuapps@hotmail.com> wrote in message news:e63cO8nyJHA.5480@TK2MSFTNGP03.phx.gbl... >I have been suing ...

Question about ordering columns based on date
Hi, I have the following table and a slight ordering problem. Each of the rows A, B, C, etc has data associated with 5 fixed dates - from 20-Dec-09 to 20-Dec-18. In addition there is one "floating" column added to the end. In this example, it is the data for column 20-Dec-10. 20-Dec-09 20-Dec-11 20-Dec-13 20-Dec-15 20-Dec-18 20-Dec-10 A 84 49 88 91 26 82 B 9 68 39 89 50 59 C 45 5 46 69 41 38 D 35 28 70 69 47 59 The dates for the "floating" column always change - it could be any date before, after or in between the five fixed dates. I'd like to sort this data so that...

Cen an Excel file be set up to no longer open after a certain date????
Any help would be very much appreciated. Is there a way to set up an excel file to not open after a certain date or after a certain amount of time? Thank you in advance!! Lou Blah wrote: > Any help would be very much appreciated. > > Is there a way to set up an excel file to not open after a certain > date or after a certain amount of time? > > Thank you in advance!! > > Lou This could be done with a VBA Macro but you would have to take security measures to keep the user from tampering with the code. Maybe even incoporate code that deletes all of the data includ...

Date changing to current date when doc reopened
When I draft a letter and save it and then reopen it later, the date always changes to the current date, even when I do not use the auto insert date. i erase the date and put it in manually but it still happens. -- Michele Michelle, sounds like you used teh insert date feature of Word. This inserts a current field, which will update every time opened. Try replacing the current date field with teh Create date field. HTH "Michele" wrote: > When I draft a letter and save it and then reopen it later, the date always > changes to the current date, even when I ...

Converting Access 2000 table to Access 97
How do you link an Access 2000 table to an Access 97 database? Thanks for your time. ...

Google Desktop index dates?
Just noticed - while trying to sort a problem with other user's Outlook not indexing after Dec16 - that on 'my side' (XP Pro) GD says my OE email was indexed up to July 2012! My pc definitely says this is Feb 2010 so what is going on? (Nobody seems to be answering postings on 'google desktop/somethingsbroken') Cheers, S I think you can uninstall it via Add / Remove Programs. steve "spamlet" <spam.morespam@invalid.invalid> wrote in message news:eBd%23TFCpKHA.3948@TK2MSFTNGP06.phx.gbl... > Just noticed - while trying to sort a pr...

Change Calender Start date
I want to add this to my code If [ckb7] = -1 Me!tbDateFrom = DateSerial(Year(Date), Month(Date) - 1, 7) Is this possible?.....Thanks for any Help............Bob Private Sub Form_Open(Cancel As Integer) Select Case Me.OpenArgs Case "OwnerStatement" Me.Caption = "Owner Statement" tbDateFrom.value = Format("01-" & Month(Now) & "-" & Year(Now), "dd-mmm-yyyy") tbDateTo.value = Format(Now, "dd-mmm-yyyy") ...

edit word art text?
I am trying to edit word art text created in an earlier version of Publisher (1998) with Publisher 2007, but it is not supported. Any ideas how to do this? The recent version will allow changes to everything but the text. There is a WordArt compatibility utility. You use it by Insert, Object, Scroll down to WordArt 3.2. If you have the old WordArt on your screen and you have installed the utility, simply double-click the WordArt. Publisher 2003/2002 Add-in: WordArt Compatibility http://www.microsoft.com/downloads/details.aspx?FamilyId=897AA11B-A37D-4586-A1A7-54BBEA375AE2&displayla...

Create Formula
I need to create a formula where I add a set number of Networkdays to a start date. Example: Start Date = 1/2/07 Number of Networkdays = 21 End Date = Is calculated If Start = 1/2/07 and 21 Networkdays are added, what is the retured end date???? I can only find example of Networkdays where I would be providing the Start and End Dates, and it will calculate the Networkdays. I can't find example where End Date is calculated based on the number of networkdays from start date. Can anyone help with a valid formula for this? Thanks!! Look for =workday() in Excel's help. (Also part o...

how to alter the date within a macro
Hello, I was trying to create an archive function on a workbook. I have a formula throughout the workbook that is based on the current date using NOW(). I went in a recorded a macro of replacing the NOW() with last years date. How can I set this so that I can have the user push a button to archive the sheet and have the year based on the current date? I want to change the replacement to an argument that gives the year -1 at the time of clicking the archive button. The current recorded code follows. Thank You LWhite Cells.Replace What:="NOW()", Replacement:="Date (...

Linking computer dates (time) to spreadsheet dates that have formu
Aloha I am hoping someone is out there who can assist me. I have spent two days trying to do two simple things in a medicine sheet I made up. It involves the dates. They need to change by the month, we have figured out the formulas that in itself was difficult. But NOW HOW do I TIE IN my computers Calendar (where the time clock) is TO the dates I have in the spreadsheet? So when the computer calendar changes the spreadsheet dates change per the formula that is in there? THank you very much Antoinette We probably need a bit more information on what you are trying to do, but ...

days calculation between dates
I would like to calculate the days based on three conditions. Here is the example of my work sheet: A B C D E F 1 Start date finish date Jan Feb Mar April 2 Jan 03, 2009 Jan 27, 2009 3 Feb 24, 2009 4 Feb 25, 2009 Mar 12, 2009 I want see the days caclulated by month for any task started. If task goes to next month, days should go to next month column and I want to calculate the weekdays only. As above row 3 date in col...

Expired date to show up automatically
In our company we have safety harnesses and they need to be recertified every year. I Would like to put the date in and then for it to show up as expired in a year with a different colour. There must be a way to do this with a formula or something? Thanks Use conditional formatting>Cell vale>Less than and enter =DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) -- Ian -- "renate" <renate@discussions.microsoft.com> wrote in message news:AB15DB4B-53E2-4CC1-B8C0-300F6008774B@microsoft.com... > In our company we have safety harnesses and they need to be recertifie...

Inserting Template truncates subject line text
When I insert a template into an email being written in CRM, it seems to truncate the original text in the subject line, and replace it with that of the template. This is not helpful, as it removes the subject line that should stay with the email, and even though i'm sure another identifier will be added to the email once sent, I have had to type out a manual subject line. An example of this is using templates to store a signature block. Since this didn't happen in 1.2, and I can;t see any way of turning it off, can anyone suggest a workaround, rather than copying & pasting...

Month End Date Ticks
Hello, In Excel 2003 I could use daily data starting on, say, 31 Dec 2005 and ending some years later for a line chart. If in the x-axis settings I selected both Major and Minor units to 3 Months with Base units as Days I would get ticks at each calender quarter end. In this example; 31 Dec 2005, 31 Mar 2006, 30 Jun 2006, and so on. However, in Excel 2007 the axis switches over to start of month ticks; 31 Dec 2005, 31 Mar 2006, 1 Jul 2006, and so on. Does anyone know what is going on with the date x-axis in Excel 2007? It seems to be a bug as I have tried all manner of settings an...

How do I insert the date the file was saved in the MS Excel foote.
I am trying to display the date the file was last saved in the footer field. the defualt date code displays the date the file was printed. I would appreciate any help with this. Hi kacate Try http://www.rondebruin.nl/print.htm#Saved -- Regards Ron de Bruin http://www.rondebruin.nl "kacate" <kacate@discussions.microsoft.com> wrote in message news:9F65885A-DC2D-4515-A0D2-5E39B7905FCF@microsoft.com... >I am trying to display the date the file was last saved in the footer field. > the defualt date code displays the date the file was printed. I would > apprec...

text color
This is my second post on this problem. Using Pub. 2002 and HP6122 printer. Created a postal card and had a color problem. Created the card and changed some of the lines to a blue ink. Saved the doc. and decided to change the blue back to black. When I printed it out it wasn't sharp. In looking under a Magnifying glass I could see the blue color edge behind the black and it makes the type fuzzy. Contacted HP and they suggested I try the same procedure in other applications as Word, Notepad, Wordpad, Works, etc. The problem does not happen in other programs. HP says it is n...

Programmatically convert lead to account
Hi, How do I programmatically convert a lead to an account? Thanks! In the SDK search on SetStateLead. This will at least allow you to set the state to qualified however I do not think it will create an account and contact so you will need to write code to create these records from your lead data. You can then use these classes to relate your lead to a contact and account TargetRelatedLeadToAccount TargetRelatedLeadToContact ============================== John O'Donnell Microsoft CRM MVP http://www.crowechizek.com/microsoft "Jeff Foster" <JeffFoster@discussions.mic...

Open worksheet by date
I have a workbook which contains twelve worksheets - January, February............., December. I would like to have the active worksheet upon opening correspond to the current month. E.g. if the workbook is opened in March then the "March" worksheet displays. How can I do this? Sandy Sandy, Copy the code below and paste it into the Thisworkbook object's codemodule. HTH, Bernie MS Excel MVP Private Sub Workbook_Open() On Error Resume Next Worksheets(Format(Date, "mmmm")).Activate End Sub "Sandy" <sandy_stephen@DELETEhotmail.com> wrote in messa...

Opening text file using Excel
When I choose Microsoft Excel Program to open a text file I get thi error "This file does not have a program associated with it fo performing this action. Create an association in the Folder option Control Panel" I used to open it few days back But I don't know what happened :-( al of a sudden it stopped working. I uninstalled and reinstalled O2k Pr with SP1 & SP3 still not working. Can someone help me out on this one. Thanks Addian -- Message posted from http://www.ExcelForum.com Hi If you right-click on the file, you should get an 'Open with . . ' option. Cli...

Entering early dates such as 3100 B.C.
How do I start the timeline at 3100 BC ? It will not accept any year before 1899. "angmont" <angmont@discussions.microsoft.com> wrote in message news:4666A577-0B63-4005-BE45-B9EC7731888C@microsoft.com... > How do I start the timeline at 3100 BC ? It will not accept any year > before > 1899. Sorry, the timeline uses the OS dates and the earliest date is around 1900 slightly after 3100BC. You could enter the dates as text, by selecting the timeline, do Open Group ( last item on the Edit menu) selecting the date text and overtyping with the new dat...

Find Text in List Box
I've looked hi and lo for code to find text in a List Box using the Find input box in AC 2007. Finding text in the form works ok but finding it in a List Box doesn't seem to work. Can anyone point me to a web site or let me know what code I might need to accomplish this? Thanks, James Hi James, List and combo boxes are objects (controls) that have row and column properties. You need to find or write a routine that visits each column of each row in turn and tests for your search string. It seems to me this is a classic case for using VBA's OOP functionality. Design a class ...

VB6 - ADO
First I must to say Sorry, because my English is not the best. I have some values that I write into an Excel XP sheet from VB6 using ADODB connection. The problem is that in Excel XP sheet the numbers in the cells are formatted as text. (All the cells In Excel have a green error indicator in the upper left corner). To write the values in Excel sheet I use INSERT INTO � or AddNew and Update methods of the RecordSet. I would like to now how can I copy the values (or one column) of the MSFlexGrid to Clipboard. It is to Past to another application, like Excel Sheet or OriginPro 7. Thanks any He...

Converting irregular Word table to spreadsheet
I am using Office Professional Edition 2003 I want to convert a list of members of a club and their contact details into a spreadsheet database. The form of each record in Word is: membership type (in upper case), name, address, phone number (if any), fax (if any), email (if any), for example ACTIVE Bill Gates Office in the Sky 25 The Long Road The Big City State, Code COUNTRY Phone: 123 4567 8910 Fax: 123 4567 8910 Email: bill@heaven.org Each line in each record terminates with a line break, and each record terminates with a paragraph mark. The ...

Convert Access 2000 to Access 97 06-22-04
I needed to convert an Access 2000 db to 97. The Converted database will run on Windows 98 but on Windows 2000 I get an error when I run some of the select queries (the select query is pulling from other queries, not tables). The error is "MSAccess.exe has generated errors and will be closed by Windows. You will need to restart the program." This is a pretty generic error and I can't seem to find anything helpful. Has anyone run into a similar problem? My main Access program, supplied in runtime 97 & 2k, does not run in A97 on Win2k or WinXP for the reason you s...