Text To Date Conversion

Hi

I run Excel 2000

I download information from a mainframe into Excel.

In cell F5 the data looks like this: 19.11.2007

This is formatted as text and is left aligned.

When I try to reformat it to a date (19/11/2007) it does not change.

I would like to have a formula in cell N5 that changes the original data to 
a datevalue. (eg: 19/11/2007)

Thanks
0
Utf
3/9/2010 1:23:01 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
923 Views

Similar Articles

[PageSpeed] 45

If column F contains a lot of the "date" values, then you could:

Data|Text to columns
Fixed width (but don't have any separator lines!)
choose Date (dmy order)
and finish up

Then give the range the date format you like.

You could even do this for a single cell, but I'd just retype it as a real date.

John Calder wrote:
> 
> Hi
> 
> I run Excel 2000
> 
> I download information from a mainframe into Excel.
> 
> In cell F5 the data looks like this: 19.11.2007
> 
> This is formatted as text and is left aligned.
> 
> When I try to reformat it to a date (19/11/2007) it does not change.
> 
> I would like to have a formula in cell N5 that changes the original data to
> a datevalue. (eg: 19/11/2007)
> 
> Thanks

-- 

Dave Peterson
0
Dave
3/9/2010 2:20:04 AM
Dave

Thanks for your prompt resonse. The data I download from the mainframe is 
usually about 500 lines so Ideally what I was after was a formula that would 
be copied down the length of the dtata.

I ended up using the following:

=IF(F5="","",VALUE(LEFT(F5,2)&"/"&MID(F5,4,2)&"/"&RIGHT(F5,4)))

This seems so work OK. Thanks very much for all your help.






"Dave Peterson" wrote:

> If column F contains a lot of the "date" values, then you could:
> 
> Data|Text to columns
> Fixed width (but don't have any separator lines!)
> choose Date (dmy order)
> and finish up
> 
> Then give the range the date format you like.
> 
> You could even do this for a single cell, but I'd just retype it as a real date.
> 
> John Calder wrote:
> > 
> > Hi
> > 
> > I run Excel 2000
> > 
> > I download information from a mainframe into Excel.
> > 
> > In cell F5 the data looks like this: 19.11.2007
> > 
> > This is formatted as text and is left aligned.
> > 
> > When I try to reformat it to a date (19/11/2007) it does not change.
> > 
> > I would like to have a formula in cell N5 that changes the original data to
> > a datevalue. (eg: 19/11/2007)
> > 
> > Thanks
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/9/2010 10:49:01 PM
Reply:

Similar Artilces:

Date filter in AFA reports
It will be important to add a date filter on AFA reports. All customers have this suggestion. Thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=d6bc6c96-14cd-4660-a7a...

Calculating Week of date
Hello - I need to find a way to obtain the date of the week (assuming day 1 of the week is Monday), so I don't have to fill out the data manually. My table should look like this -- Date Week of 11/17/2008 11/17/2008 11/18/2008 11/17/2008 11/19/2008 11/17/2008 11/20/2008 11/17/2008 11/21/2008 11/17/2008 11/22/2008 11/17/2008 11/23/2008 11/17/2008 11/24/2008 11/24/2008 11/25/2008 11/24/2008 11/26/2008 11/24/2008 11/27/2008 11/24/2008 11/28/2008 11/24/2008 11/29/2008 11/24/2008 11/30/2008 11/24/2008 I will provide the date, but need a formula so it will calculate the corr...

Macro to change flag reminder date +/or time on many messages at once?
This is one I haven't tried to fix before. I have a special use for Outlook. Besides regular messages, I send myself "To Do's" to and from home marked with a special phrase when it's a To Do that has rules that mark it with a flag, etc. In either work or home, a lot of those To Do's need to be deferred to a different time or day. It's been tedious as I have to change each manually. Now I made it easier some time ago by putting a flag option right on the toolbar so at least I don't have to open each message. The preview pane is on for this folder and I th...

Text size #2
Hello friends, is there a way to change the default text size and the font for the incoming emails in OutlookXP, the email format is set to HTML with Word as the email editor. Appreciate your help. Thanks. ...

Data Conversion into Sybase
Hello. We have a very large Excel spreadsheet that we want to convert into a Sybase Database. We are wondering if anyone had a good suggestion as to go about doing this. We've thought about converting the data into MS Access first and then going to Sybase, but using the intermediate program doesn't seem like it is the best idea. Also, we're only converting about 1000 rows now, but there are many, many more that will need to be converted later on. Any sugggestions would be appreciated. Thanks. Hi I would try to save the Excel files as *.csv file. Sybase should be able to...

Date Time Stamp in Table
I have a field in my table called "Status' can I add another field to my table that gives a date and time to when I changed the field in each record in Status, [Status] being a text field options Active;Finished ......... thanks for any help with this..Bob ...

inserting a text into xml file
Hi friends, I am new to csharp and XML : fp=File.OpenText(server.mappath(".\\upload\\")+"test.txt"); string info=fp.readtoend(); string[] arinfo =new string[4]; char splitter = {'|','^'}; arinfo =info.split(splitter); for(int x=0;x<arinfo.length;x++) { response.write(arinfo[x]+"<br>"); a=arinfo[x]; xmltextwriter xwriter=new xmltextwriter("C:/XML/Data.xml",system.text.encoding.UTF8); xwriter.writestartdocument(true); xwriter.writestartelement("login"); xwriter.writeelementstring("usernam...

conversion from biz to deluxe caused odd problem
I had money 2003 biz version and was only using the personal section (no business entries). i bought a new computer, which had money 2003 deluxe. i exported from the biz edition, and imported into deluxe. seemed to import all of the data. when i look at an account in 'all transaction' mode, the balances are correct. but when i look at an account in 'unreconciled transactions' the balances are in the trillions! same when i try to balance an account or look at the account list. it's nice to think that i have a lot more money than bill, but it looks like a ser...

insert table into connecting text box w/link
How do I insert a rather large table (single column, many rows) into text boxes which overflow from page to page? You don't... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "display name" <display name@discussions.microsoft.com> wrote in message news:B397C80A-B72D-4D1B-B106-E14E3222A331@microsoft.com... > How do I insert a rather large table (single column, many rows) into text > boxes which overflow from page to page? ...

Auto Formatting Cells and Dates
I noticed that if you type in something like 3/16 excel will assume you meant March 16th and change the cell to a date. I know you can change the format to text before you type the date in. Can you turn it off completely? Are there any options you can change concerning this feature (like the date format it changes to). Can you create your own autoformats concerning numbers other than dates? preformat the cell as Text or start your data entry with an apostrophe: '3/16 Sloth wrote: > > I noticed that if you type in something like 3/16 excel will assume you meant > Ma...

pivot table
hi Though I have been using Excel for quite a while. I just did my first pivot table today. I need a report based on specific date (Actually it is every Sunday). The date I have entered are Nov 1, 2009 and Nov 8, 2009) in the Excel but when I used date as the page report filter. The date filter is <Nov 1 > Nov 9 with many dates in between. How do I make it to show only Nov 1, 2009 and Nov 8, 2009. I really very happy because pivot table really useful tool except for my problem re date. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/exce...

Quicken 2004 conversion #2
I had the same problem. I can convert an older 2003 file but it is not current. My Quicken 2004 file will not convert. >Subject: Quicken 2004 conversion >From: "JohnW" jandjwilloz@cox.net >Date: 9/13/03 8:59 PM Eastern Daylight Time >Message-id: <031501c37a5b$7acd66c0$a401280a@phx.gbl> > >I had the same problem. I can convert an older 2003 file >but it is not current. My Quicken 2004 file will not >convert. > > > You CANNOT convert from Quicken 2004 to ANY Money year thru the Money converter. I suggest you convert from your last Quicke...

User Defined conversions
How I can make classes compatible with each other without inheritance but using casting? Ex: class Me { } class You { } Me m=new Me(); You u=(You)m; I shall be able to access members of m through u Could not find much information anywhere! Thank you Regards Raj Raj wrote: > How I can make classes compatible with each other without inheritance but > using casting? You'll have to define "compatible" more precisely. In C#, there is actually a such thing as "user defined conversion", per the subject you gave this message ...

date difference expression
I am using the DateDiff expression to obtain the difference between 2 dates.However I can't get it to run.How exactly is it entered into the query ..I am using MS 2002 -- Insurance Guy PJ wrote: > I am using the DateDiff expression to obtain the difference between 2 > dates.However I can't get it to run.How exactly is it entered into > the query .I am using MS 2002 AliasName: DateDiff("x", FirstDateField, SecondDateField) ....where x is the interval you want to count ("d" = days, "h" = hours, etc.). For more details see the help topic for t...

Storing current dates and system username on a cell
I need a code either a formula or better still a sub procedure o function for storing a current date value which cannot alter after tha date and the username(from the system) on a cell automatically onl after the user has clicked the saved button. For example Date (to be automatated) :username(to be automatated) 08/07/2004 : Jo freazer 09/07/2004 : Kelly hamburger 10/07/2004 : Tai Azi 11/07/2004 : Kelly hamburger Thank yo -- Message posted from http://www.ExcelF...

Assistance Requested for Date Arithmetic
I've been struggling with this scenario for many hours now and am hopeful someone on this group can provide an assist. Consider the following known data: 2005 Annual Invoice Date 2005 Annual Invoice Amount 2006 Annual Invoice Amount I need to create a monthly amortized view of this data (through 2007), taking into consideration that the invoice month is one month in advance of the booking month. For instance: A customer was billed $12000 on 3/1/05 for an annual contract. This invoice would be amortized $1000 per month for 12 months starting 4/05 and ending 3/06. The renewal in 2006...

Re: Macro that shows range of dates
Oops. Change to for a1 & a2 Sub makedates() Dim i As Long For i = 1 To Range("a2") - Range("a1") + 1 Cells(i, "d") = Range("a1") - 1 + i Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Don Guillett" <dguillett1@austin.rr.com> wrote in message news:... > Option Explicit > Sub makedates() > Dim i As Long > For i = 1 To Range("b1") - Range("a1") + 1 > Cells(i, "d") = Range("a1") - 1 + i > Next i > End Sub >...

text boxes
I have a unbound text box on a form that's not in a query how do i bind that to a report? Can't be done. Tell use what you want to do and perhaps we can tell you how. -- Dave Hargis, Microsoft Access MVP "DJACKSON" wrote: > I have a unbound text box on a form that's not in a query how do i bind that > to a report? I have a label sticker report based on a select query and I would like to add text to the sticker. The add text would change constantly so I would like to enter that on a form through a text box. Is there any hope? Thanks for your help "Kl...

Show Date & Time when Item Enters Queue
Is there anyway of modifying the Queue Item entity? In both CRM 3.0 & 4.0 it is a non-customizeable entity. Our client is using 4.0. They have Cases being dropped into a queue via remote webservice. The customer support team is monitoring this queue and it's necessary to display not just the date it entered the queue but the date and time it entered the queue for priority purposes. The Queue Item "enteredon" field is a datetime field but the format is currently set to Date Only. Can this be changed? We can write a custom page with an IFRAME and replace the Queue...

Separating texts separated by commas in a cell
Please I would like to know how to separate text separated by commas in cell. For example; In a cell containing the text v1,v2,T1. i want t write the texts "v1" , "v2" and "T1" in separate cells. Thank you -- Message posted from http://www.ExcelForum.com somto Data>Text to Columns>De-limited by comma. Gord Dibben Excel MVP On Wed, 21 Apr 2004 20:21:53 -0500, somto <<somto.153bif@excelforum-nospam.com>> wrote: >Please > >I would like to know how to separate text separated by commas in a >cell. For example; In a cell containin...

Combo box show as text box
I have several forms that have Combo Box fields that I have set to Enabled=no, Locked=yes. On the form even though its not selectable it shows the dropdown selector on the right side of the box. Is there a way to hide the dropdown selector to make it just look like a text box? Thanks for any imput. ...

combine text from 2 cells HELP! PLEASE!
I have text in cell A and Cell B. I would like to add the text in cell B to the end of the text in cell A while seperating them with a | (pipe). One way: =A1&"|"&B1 or if you want spaces on both sides of the pipe: =A1&" | "&B1 HTH, ryanb. "John Adams" <johnadams@comcast.net> wrote in message news:nk17sv0h9j37coum79h30ac071gr7o8jdt@4ax.com... > I have text in cell A and Cell B. I would like to add the text in cell > B to the end of the text in cell A while seperating them with a | > (pipe). > John, Try a formula like ...

I have text in an Excel Cell that isn't showing all of it?
I have Text in Excel that will not display..meaning its a lot of text and only part is showing. I have selected wrap text and auto fit for column and row height. I can expland the cell but the text wont show (all of it)...it shows up to a certain point. Any Ideas?? This is from a reply written by Cyber Taz. The question is similar to yours. Cells were never intended to store large volumes of text. They have a limit of 409 points in height (roughly 34 lines of 10 pt. type, assuming 120% leading). There is also some technical glitch with content that falls within the 256-1024 character...

Using Sumproduct and Dates
I am currently working on a workbook that has various part nmbers (2060207-WPI001) on work sheet 7-14-07 in E:E in which I match a 1 to 3 letter designations as in "WPI". In column AI:AI is the dates which these pieces are scheduled to cast. I need to place a date in cell H3 of sheet 1 as 8/30/07 and sum the total occurences which are less than or equal to the 8/30/07 date. When I use the following I return the total occurences were WPI is listed but I need the sum of those with dates less than or equal to 8/30/07 for billing purposes. =SUMPRODUCT(--(ISNUMBER(FIND({"WPI"}...

Modify Cheque Date format
Hi, I am trying to modify the cheque date. I need to remove the dash from my cheque format and I was hoping someone might have some insight. Thanks! Not sure which date format you are starting with, but if using Report Writer try double clicking the DocumentDate field and choose a different format - or create a new format. Online help has details. ...