Pasting data - some appears in date format?

    This


    COMP RND MAR HOME PTS AWAY PTS
    HA 1 23 Bris 18-8-116 St K 13-15-93
    HA 1 20 Kang 16-9-105 Carlt 12-13-85
    HA 1 35 Frem 13/10/1988 Port Ad 7/11/1953
    HA 1 46 Melb 15-13-103 Ess 8/09/1957
    HA 1 63 Sydn 18-10-118 Hawth 8/07/1955
    HA 1 11 Adel 7/11/1953 West C 9/10/1964
    HA 1 62 Rich 15/08/1998 Geel 25-10-160
    HA 1 32 Coll 11-14-80 Wstrn B 16-16-112


    came from this -


    COMP RND MAR HOME PTS AWAY PTS VENUE ATTEND DATE TIME
    HA 1 23 Bris 18-8-116 St K 13-15-93 Gabba 33,369 24-03-2005 19:45
    HA 1 20 Kang 16-9-105 Carlt 12-13-85 Telstra Dome 40,345 26-03-2005 14:15
    HA 1 35 Frem 13-10-88 Port Ad 7-11-53 Subiaco Oval 33,704 26-03-2005 18:45
    HA 1 46 Melb 15-13-103 Ess 8-9-57 MCG 47,849 26-03-2005 19:20
    HA 1 63 Sydn 18-10-118 Hawth 8-7-55 SCG 27,274 27-03-2005 13:10
    HA 1 11 Adel 7-11-53 West C 9-10-64 AAMI Stadium 39,055 27-03-2005 14:15
    HA 1 62 Rich 15-8-98 Geel 25-10-160 MCG 48,251 27-03-2005 14:15
    HA 1 32 Coll 11-14-80 Wstrn B 16-16-112 MCG 53,259 28-03-2005 14:15



    How can I stop the fomat 7-11-53 coming out as 7/11/1953 please?


    Any help would be much appreciated.

    Re: Pasting data - some appears in date format?


    The problem is that Excel is being 'clever' and recognising the dates.


    Try formatting the destination cells as text first and then pasting values.


    HTH,


    Alan.
    Are the Aussie Football results with ground, attendance, date?
    A.

    Re: Pasting data - some appears in date format?


    I selected all


    formatted cells as text


    didn't work, cant work out why some scores come out as dates.


    maybe its the original data?


    COMP RND MAR HOME PTS AWAY PTS VENUE ATTEND DATE TIME
    HA 1 23 Bris 18-8-116 St K 13-15-93 Gabba 33,369 24/03/2005 19:45
    HA 1 20 Kang 16-9-105 Carlt 12-13-85 Telstra Dome 40,345 26/03/2005 14:15
    HA 1 35 Frem 13/10/1988 Port Ad 7/11/1953 Subiaco Oval 33,704 26/03/2005 18:45
    HA 1 46 Melb 15-13-103 Ess 8/09/1957 MCG 47,849 26/03/2005 19:20
    HA 1 63 Sydn 18-10-118 Hawth 8/07/1955 SCG 27,274 27/03/2005 13:10
    HA 1 11 Adel 7/11/1953 West C 9/10/1964 AAMI Stadium 39,055 27/03/2005 14:15
    HA 1 62 Rich 15/08/1998 Geel 25-10-160 MCG 48,251 27/03/2005 14:15
    HA 1 32 Coll 11-14-80 Wstrn B 16-16-112 MCG 53,259 28/03/2005 14:15

    Re: Pasting data - some appears in date format?


    COMP RND MAR HOME PTS AWAY PTS VENUE ATTEND DATE TIME
    HA 1 23 Bris 18-8-116 St K 13-15-93 Gabba 33369 38435 0.822916667
    HA 1 20 Kang 16-9-105 Carlt 12-13-85 Telstra Dome 40345 38437 0.59375
    HA 1 35 Frem 32429 Port Ad 19670 Subiaco Oval 33704 38437 0.78125
    HA 1 46 Melb 15-13-103 Ess 21071 MCG 47849 38437 0.805555556
    HA 1 63 Sydn 18-10-118 Hawth 20278 SCG 27274 38438 0.548611111
    HA 1 11 Adel 19670 West C 23659 AAMI Stadium 39055 38438 0.59375
    HA 1 62 Rich 36022 Geel 25-10-160 MCG 48251 38438 0.59375
    HA 1 32 Coll 11-14-80 Wstrn B 16-16-112 MCG 53259 38439 0.59375

    Re: Pasting data - some appears in date format?


    OK, what has happened is Excel has still treated it as a date and then shown you the serial number!


    We can keep trying to do this but it will be a recurring problem.


    Can you give me the URL that you are using to get the data?


    What do you want to do with the data?


    A.

    Re: Pasting data - some appears in date format?


    Dougk,


    Open a new workbook.
    Select all cells and format them as text


    Open Word
    Highlight the table and copy it from the web.
    Paste it into word


    Highlight the data in word
    Copy


    Pastevalues into Excel


    A real pain but it works.


    I suggest that you then insert a couple of columns and split the scores into three columns using the Text to columns on the Data menu.


    Good luck,


    Alan.

    Re: Pasting data - some appears in date format?


    doug


    Have you considered using Data>Get External Data...Web query to download this data?


    I tried that and managed to turn off date conversion using the Advanced... options.

    Boo!:yikes:

    Re: Pasting data - some appears in date format?


    Norie,


    I tried this and the page would not refresh. Have you managed to get it to refresh? It does seem the right solution but only if the data will come through!


    A.

    Re: Pasting data - some appears in date format?


    Doug,


    I would love a golf lesson. I hardly get toplay anymore. The only problem is that your website does not say where in Australia you are (and that is only clear from the first paragraph on the About page) so booking my ticket from the UK could be a problem. The other one is my wife might not agree... :)


    A.

    Re: Pasting data - some appears in date format?


    A


    What do you mean by refresh?


    Can't you just right click and select Refresh?


    Or set the appropriate query properties to refresh automatically.

    Boo!:yikes:

    Re: Pasting data - some appears in date format?


    Norie,


    When I firt loaded the page in teh query designer it too a while but worked. Then when I clicked refresh on the query it simply hung for ages.


    When I have loaded the pages in IE it also struggled and I had some silly adverts . I do not know if these caused a problem for Excel.


    No need to worry abot it though, I have never had this problem with queries I have wanted to do.


    Cheers,


    Alan.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login