Excel number / date conversion

I've just pulled some data out of an ODBC connection into Access an
then exported it to Excel.

The dates are currently in the format 20031109. But the built in dat
convertor (from Format Cells) doesn't do anything, even if I convert i
to a 'proper' number first.

How can I convert this to 09/11/2003 so that I can do some date - base
arithmetic and work out difference in days

--
markperr
-----------------------------------------------------------------------
markperry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2759
View this thread: http://www.excelforum.com/showthread.php?threadid=47112

0
9/27/2005 3:39:59 PM
excel 39879 articles. 2 followers. Follow

3 Replies
3295 Views

Similar Articles

[PageSpeed] 11

Hi

Use this formula
=DATE(MID(A1,1,4),MID(A1,7,2),MID(A1,5,2))


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"markperry" <markperry.1w0z2b_1127837104.7017@excelforum-nospam.com> wrote in message 
news:markperry.1w0z2b_1127837104.7017@excelforum-nospam.com...
>
> I've just pulled some data out of an ODBC connection into Access and
> then exported it to Excel.
>
> The dates are currently in the format 20031109. But the built in date
> convertor (from Format Cells) doesn't do anything, even if I convert it
> to a 'proper' number first.
>
> How can I convert this to 09/11/2003 so that I can do some date - based
> arithmetic and work out difference in days?
>
>
> -- 
> markperry
> ------------------------------------------------------------------------
> markperry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27593
> View this thread: http://www.excelforum.com/showthread.php?threadid=471121
> 


0
rondebruin (3790)
9/27/2005 4:26:19 PM
Select the column and Data>Text to Columns>Next>Next>Column Data
Format>Date>YMD(YDM?)>Finish.


Gord Dibben Excel MVP



On Tue, 27 Sep 2005 10:39:59 -0500, markperry
<markperry.1w0z2b_1127837104.7017@excelforum-nospam.com> wrote:

>
>I've just pulled some data out of an ODBC connection into Access and
>then exported it to Excel.
>
>The dates are currently in the format 20031109. But the built in date
>convertor (from Format Cells) doesn't do anything, even if I convert it
>to a 'proper' number first.
>
>How can I convert this to 09/11/2003 so that I can do some date - based
>arithmetic and work out difference in days?

0
Gord
9/27/2005 5:19:44 PM
Absolute genius - you've saved me a lot of head scratching.

Thanks

--
markperr
-----------------------------------------------------------------------
markperry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2759
View this thread: http://www.excelforum.com/showthread.php?threadid=47112

0
9/28/2005 10:50:31 AM
Reply:

Similar Artilces:

How do I toggle between worksheets in the same Excel file ?
Please tell me the keyboard shortcut to toggle between worksheets in the same Excel file. My OS is XP and am using Excel 2000. Ctrl+PageUp and Ctrl+PageDown Regards Rowan Sameer wrote: > Please tell me the keyboard shortcut to toggle between worksheets in the same > Excel file. My OS is XP and am using Excel 2000. ...

Opening Excel #5
When opening excel 10 different audio files have attached themselves to the start of each file. It's like the New Cd with Rod Stewart have transferred to excel. When launched, XL opens (or attempts to open) all files in your XLStart directory and, if an additional folder is designated, the files in Tools>Options>General Tab>At startup, open all file in___. Most users leave this option blank and remove all non-essential files from XLStart. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "GOLFBALL49" <anonymous@discussions.microsoft.com> wrot...

Excel 97 / Excel 2000 compatibility problem?
Hello, I=B4m developing an Excel application with Excel 97. I have added a control toolbox of type textbox to one of=20 its worksheets. Until now it was no problem to run it with Excel 2000. But in the latest version the textbox disappears in Excel=20 2000. It is still visible on the sheet, but its name is=20 not more known in the Excel-VBA-Code nor is it visible in=20 the editor windows class list. Has anybody an explanation for this behaviour? Regards Michael ...

growing excel database
Hello everyone! It is possible to import data into excel from the web. It is also possible update this data IN THE SAME CELLS. In other words, the values that once occupied the cells are replaced with new values. That is a problem for me. I would like updated information to be pasted in rows above the old data. Is this possible? If so, how? Piotr, Yes, it is possible to import data into Excel from the web. Web queries are designed to do this and if a web query can't do what you need, then there are ways to automate Internet Explorer (i.e. IE Automation). You can record a m...

Sorting issue
Hello, I am having a problem sorting two columns (one is the street name and the other is the street number eg. 321 Smith St) in Excel 2007 by using a custom sort. Let's say that the data in column A is the number and B is the street name. I have the sort setup to sort first by B then by A. The problem is with the numbers. Some numbers are out of order in the spreadsheet although the majority are correct. I have formatted the number column (A) as a number as well. I have also tried creating a new spreadsheet and pasting (paste special) the data into it with the same result. Can anyo...

Excel sheet
I want to increase value by 1 of every cell which I entered by using formula. How can do this? I want automatic result in excel I am getting problem by this You are in a Microsoft Publisher ng. But if you want to number a column, i.e., line numbers, this may be what you want In A1 put 1 In A2 put =1+a1 Now copy A2 down to whatever you want. I think this is how it goes in Excel; I'm a Lotus 1-2-3 user and the formuli are a bit different. Have a Merry! -- Don Vancouver, USA "vasanth" <u40119@uwe> wrote in message news:7d26017dc9997@uwe... >I want to incr...

Strange Excel behaviour
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi there, <br> I'm writing to you because I'm driving crazy with excel. <br> Everything works quite fine, but, when entering numbers in a cell, Excell automaticly divides it per 100!! so I enter for example 4, and it writes 0,04!! <br> any idea about the reasons why?? <br> thanks a lot <br> Carlo Well, sure... And if you'd queried the group before posting you'd know why as well :-) It's been asked & answered numerous times: Go to Excel> Preferences&...

complicated excel template
I am on the beginning phases of a manufacturing template. Hopefully som of you can help me out. I need to determine how many parts of a certai item I can obtain from a determined square foot of material. Fo example: how many parts of 1" x 1.5" widgets can I get from a 48" x 96 sheet -- Message posted from http://www.ExcelForum.com You could use something simple like: =INT(C1/A1)*INT(D1/B1) where A1 = part width, B1 = part length, C1 = sheet width, D1 = sheet length. Of course this does not take into account any unique cutting patterns that minimize scrap. It's assume...

Planning training in Excel
I have been tasked to organise training for my company and they have asked me to use MS Project, which has totally blown my mind. Having done some investigating Project will not give me the answers that I need surprisingly, but I am hoping Excel will I am really trying hard to figure a way of schedulign training for 200 individuals, with 20 different modules (fixed dates) and take into account holidays. So for instance Joe Bloggs needs to attend Module A, B & D over the next 3 months, but he also has 2 weeks holiday booked. There are four other people in his department but I can only all...

Auto-changing of Excel Files' name
Ever since we changed our server's O/S from Windows Server NT 4.0 to Windows Server 2003, all excel files' name will get changed by itself when they are accessed from any terminal within the LAN, which is running Windows 98 or Windows XP. What could be the cause(s) of this strange problem? Any solution? ...

Create drop down list in Excel 2007
Hi Could you please share with me how to create drop down list in Excel 2007? Thanks in advanced What version of Excel are you using? "lee-sc" wrote: > Hi > > Could you please share with me how to create drop down list in Excel 2007? > > Thanks in advanced > Sorry Lee-sc, hit the send button to quick in my previous post. In the Data menu, choose "Data Valiation", then "Data Validation". Under Settings, select "List" in he "Allow" drop down box. In the "Source" field, type in or se...

Date Calculation
Hi I have an unbound box on my form which shows how many days are left between the dates, 'Project Arrived' and 'Project Required' this works great. What I want to know is whether I could get this box to stop counting when a third date, 'project complete' is filled in? Any advice would be greatly appreciated Thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1 In order to help we need to see the code and the code's location that you're using to do this calculation. -- There's A...

how can i set up an automatic "date update" in excel?
In other words, i would like excel to update this cell with the current date every time when i open the document. Can i do that? one way: =TODAY() In article <D8818AFF-8489-4FF1-B94D-A08FB0B45F01@microsoft.com>, "Litzyam" <Litzyam@discussions.microsoft.com> wrote: > In other words, i would like excel to update this cell with the current date > every time when i open the document. Can i do that? One way =today() HTH "Litzyam" wrote: > In other words, i would like excel to update this cell with the current date > every time when i open t...

Excel 95 to Excel 2003
Anyone know if it's possible to convert Excel 95 files to Excel 2003? If so, how. Thanks in advance Open the file in 2003 and SAVE as you would a file created in 2003. If not absolutely positive, save as a different name. -- Don Guillett SalesAid Software donaldb@281.com "Cooper" <kcooper2@austin.rr.com> wrote in message news:uqLpEuG4EHA.1292@TK2MSFTNGP10.phx.gbl... > Anyone know if it's possible to convert Excel 95 files to Excel 2003? If > so, how. > > Thanks in advance > > Open in XL 2003, when you close it, it should, ask you if you w...

Exporting the subreports to an excel
Hi , once i am able to create the sub reports when i export this to an excel i am not able to see the data.. can you please help me out with this.. Thanks in Advance Kasaiah ...

Excel appearance
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel when I open up a worksheet the view is too small so I zoom to 150% which is perfect. I cannot find a way to keep this preference as my default when opening other worksheets. i am a new user for Mac. thanks You can't do this in Excel 2008 because it has no VBA. Each workbook will open at the zoom ratio it was last saved at. In the next version of Excel, you will be able to make a simple macro to set the zoom on opening. Cheers On 17/03/10 5:20 AM, in article 59bb557f.-1@webcrossing.JaKIaxP2ac0, ...

Manager Error 'Bad File name or Number'
I keep getting this message on one of my machines when I try to open manager. Admin and POS both open and operate fine. I have uninstalled and reinstalled twice to no avail. Any help would be much appreciated. Thanks. Open Administrator and go to File/Configuration. Check the Paths and Templates Tabs - you probably have an invalid path or filename in one of those fields. If you are using files shared on the network, you may not have permission to access the files or folders. -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ----------------------...

Generated Excel 97 documents limited to one worksheet
Hi everyone, I read in several articles that if I want to generate an Excel 97 document dynamically, using ASP for example, by setting the content type to application/vnd.ms-excel, I read that in this case, the document is limited to one worksheet and the user will only be able to view one document max. at a time. Actually, I tried it myself, and it's true. The moment I open a second browser window and try to access the same page that generates the Excel document, the first page goes blank. My quetion is, whether there's an official Microsoft document of MSDN KB article that states t...

numbering macro
In a database of say 10 columns and 100 rows, how do I record a macro that will o Insert a new column to the left of column A, then o in the new column A, number the rows that contain data in column B. Note: The numbering should start from 1. Since row 1 has the database heading & row 2 has the column headings, the numbering (no. 1) should start from row 3 and continue down to the last row that has data in column B. Columns("A:A").Insert For i = 1 to 100 If Cells(i,"B").Value <> "" Then Cells(i,"A&q...

Excel formula bar
I want to insert in my formula bar the = sign as an function with my mouse - as I was able to do with my older excell. The 2003 excel shows the fx sign, which I do not want Thanks. Hi Jacko! Try: Tools > Customize > Commands Tab Under Insert you'll find the = sign that you can drag to a toolbar. It doesn't work the same as earlier versions of Excel but does what you ask. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Jacko Maree" &...

put numbers back on a calendar template after removing shading?
O n the Event Schedule Planner template, when I remove shading from the calendar the numbers are removed also. It won't let me put them back. When I use "no fill" it removes the numbers too. Exactly what template are you using? What version Publisher? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "dfarnelli" <dfarnelli@discussions.microsoft.com> wrote in message news:162D36F1-E593-4EB2-B0A2-F769CBE0C1C0@microsoft.com... > O > n the Event Schedule Planner template, when I remove shading from the...

Entering More than 15 numbers
I'm trying to import numbers that are 25 characters in length. When I do this (or even if I enter over the 15 character limit) I get an overflow number. How do I enter or import numbers greater than 15 digits and have it display the entire number. I am using Office XP. precede with an apostrophe to get text, Excel's limit for numbers is 15 digits precision -- Regards, Peo Sjoblom "Mike" <anonymous@discussions.microsoft.com> wrote in message news:308101c428be$93500a10$a101280a@phx.gbl... > I'm trying to import numbers that are 25 characters in > lengt...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

Excel to Excel Automation
Could someone please explain the best process for taking data that has been typed into certain cells in spreadsheet and sending this information to another spreadsheet, in a different location.. Is this possible thorough VBA. Thanks for any advice.. Larry Bargers -- lbargers ------------------------------------------------------------------------ lbargers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32798 View this thread: http://www.excelforum.com/showthread.php?threadid=526211 You are really better off using the destination to pull the info from the ...

Excel 2002 and Excel 2000
Im pretty sure that this is a issue between the two different versions. I have file that was created in Excel 2002 and when it is opened in Excel 2000 the page breaks are different. What was supposed to be a one full page document in 2002 is about a third page in excel 2000. Anyone else seen this? Its not a problem for me to change the page breaks but for some other users who know nothing about computers its more than what they want to do. Could it be the way the document was made? ...