+ Reply to Thread
Results 1 to 13 of 13

Excel is converting numbers to date ....arrgh!

  1. 01-11-2007, 08:48 AM #1
    tossan
    tossan is offline
    Registered User
    Join Date
    01-11-2007
    Posts
    6

    Excel is converting numbers to date ....arrgh!

    I have a HTML table with a column with numbers like

    3.6
    2.5
    1.8
    ....

    and so on...

    When I'm trying to open this table in Excel (.csv or if I cut n paste from HTML) Excel keeps converting it to date format.

    03.jun
    02.may
    01.aug


    This wouldn't be a problem if I could only go to Format -> Cell and choose Standard or something.

    The problem is it ends up like this then:

    39236
    39204
    39295


    WTF? :D

    I want this!!!

    3.6
    2.5
    1.8
    ....


    Please help me.. thank you!
    Reply With QuoteRegister To Reply
  2. 01-11-2007, 09:27 AM #2
    oldchippy
    oldchippy is offline
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    See link to previous

    http://www.excelforum.com/showthread...=numbers+dates
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these
    Reply With QuoteRegister To Reply
  3. 01-11-2007, 11:09 AM #3
    tossan
    tossan is offline
    Registered User
    Join Date
    01-11-2007
    Posts
    6
    Quote Originally Posted by oldchippy
    See link to previous

    http://www.excelforum.com/showthread...=numbers+dates
    Thank you.


    However, the problem with importing as text is that it will not go back to numbers and the auto sum and such will not work with text.

    Reply With QuoteRegister To Reply
  4. 01-11-2007, 11:35 AM #4
    MDubbelboer
    MDubbelboer is offline
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    Quote Originally Posted by tossan
    Thank you.


    However, the problem with importing as text is that it will not go back to numbers and the auto sum and such will not work with text.

    i'm not sure this will actually work, but i just found this link for another post and it seems relevant

    http://www.contextures.com/xlDataEntry03.html

    i'll do a bit of testing to see
    --Mark

    Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
    Reply With QuoteRegister To Reply
  5. 01-11-2007, 11:47 AM #5
    tossan
    tossan is offline
    Registered User
    Join Date
    01-11-2007
    Posts
    6
    Quote Originally Posted by MDubbelboer
    i'm not sure this will actually work, but i just found this link for another post and it seems relevant

    http://www.contextures.com/xlDataEntry03.html

    i'll do a bit of testing to see

    I tried the "Convert with Paste Special", but it didn't work here.

    I'm running Excel 2003 (11.5612.5606), Norwegian btw.
    Reply With QuoteRegister To Reply
  6. 01-11-2007, 11:48 AM #6
    MDubbelboer
    MDubbelboer is offline
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    can i see a portion of your csv

    you might have to zip it to upload as it doesn't like many extensions

    i'm having a bit of a hard time replicating your problem...
    Reply With QuoteRegister To Reply
  7. 01-11-2007, 12:24 PM #7
    tossan
    tossan is offline
    Registered User
    Join Date
    01-11-2007
    Posts
    6
    Quote Originally Posted by MDubbelboer
    can i see a portion of your csv

    you might have to zip it to upload as it doesn't like many extensions

    i'm having a bit of a hard time replicating your problem...

    Basically it's a report from Google Adwords.. I've zipped & uploaded a little part of it.

    Check out the columns "Avg CPC" and "Avg Position".

    Thank you so much for taking the time...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 01-11-2007, 12:55 PM #8
    MDubbelboer
    MDubbelboer is offline
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    well i'm proving to be rather useless on this front because that csv file opens properly with no issues for me...
    excel 2003, 11.8105.8107 SP2

    sorry....
    Reply With QuoteRegister To Reply
  9. 01-11-2007, 07:16 PM #9
    oldchippy
    oldchippy is offline
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Works OK for me too - excel 2003, 11.8105.8107 SP2
    Reply With QuoteRegister To Reply
  10. 01-12-2007, 03:46 AM #10
    tossan
    tossan is offline
    Registered User
    Join Date
    01-11-2007
    Posts
    6
    Hm thanks a lot guys.

    I will try it out on some other computers when I get the chance.

    Is it possible my Excel needs an update?

    Thanks.
    Reply With QuoteRegister To Reply
  11. 01-12-2007, 04:17 AM #11
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by tossan
    Hm thanks a lot guys.

    I will try it out on some other computers when I get the chance.

    Is it possible my Excel needs an update?

    Thanks.
    Hi,

    One thing that might help you is to rename the file as .txt, and (in Excel) do the File, Open and it walks through the Text-to-Columns script, accept the first two screens, and at the columns screen select each column in turn, note the first item within the column that gives the 'Date' format, and select 'General' (not 'Date')

    As you are running a 2003 version it should produce the correct result, however if your system default date (or Excel date?) is being mis-interpreted as d.m (rather than d/m/y) then you might get the results that you describe.
    (and yeah, I have no idea how that might be)

    Hope this helps
    ---
    Si fractum non sit, noli id reficere.
    Reply With QuoteRegister To Reply
  12. 01-12-2007, 04:58 AM #12
    tossan
    tossan is offline
    Registered User
    Join Date
    01-11-2007
    Posts
    6
    GOT IT!

    Went to Control Panel and settings for Region and Language.

    Set it to English instead of Norwegian and now it works fine.

    The problem is (obviously :D) that with the european system time (eg. 03.03.04, and not 03/03/04 like in the US) Excel reads numbers like 3.3 like a date.

    A major bug if you ask me.

    So happy it works now though!! I can finish my reports before the weekend and my boss will be happy! :D


    Thanks a lot guys.. this is a excellent forum.
    Reply With QuoteRegister To Reply
  13. 01-12-2007, 08:03 PM #13
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by tossan
    GOT IT!

    Went to Control Panel and settings for Region and Language.

    Set it to English instead of Norwegian and now it works fine.

    The problem is (obviously :D) that with the european system time (eg. 03.03.04, and not 03/03/04 like in the US) Excel reads numbers like 3.3 like a date.

    A major bug if you ask me.

    So happy it works now though!! I can finish my reports before the weekend and my boss will be happy! :D


    Thanks a lot guys.. this is a excellent forum.
    good to see that you have this solved, a strange occurance that I am suprised has not been noticed before.

    Thanks for the response.
    ---
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1