Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date format won't change..

    Sorry, I'm a native of the Access forum... So forgive my question... I have a table I'm importing into Access and one column, a date column, is giving me problems. I need the format to be all the same but there are quite a few records that are different: some are mm/dd/yyyy and some are mm/dd/yy. The ones that are mm/dd/yy are giving me problems and won't convert. However, when I double-click on them, they change to the correct format after a moment???
    Can anyone tell me how to apply the correct format? All I thought I had to do was to select them all, right-click & format them all to the same date. Why do some not change?

    Thanks!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date format won't change..

    Hi,
    Could you attach a scaled down copy of your spreadsheet? (100k maximium size)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format won't change..

    Hi,

    It sounds as if you need to format the cells. Highlight the column, them Format, Cells, select the "Number" tab, and select "Date" from the Category box- then choose your format.

    Does that correct the problem?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    New Lounger
    Join Date
    Sep 2006
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format won't change..

    Actually, that is my problem... Doing exactly what you described above was not changing the cells formatting. Can you tell me in what situations it might not change it so I might be able to write a macro to fix?

    Thanks!

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format won't change..

    MDC,

    Without seeing, it's a bit hard to answer.

    Can you do as Rory suggested and attach the spreadsheet? It sounds as if (possibly) you might be able to create a very small spreadsheet by cutting and pasting the affected area into a new sheet.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    New Lounger
    Join Date
    Sep 2006
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format won't change..

    Okay, I've cut and pasted some of the data. Again, some of the dates import okay from here (the mm/dd/yyyy) and any others do not. It is attached: tblSample.xls
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date format won't change..

    Some of the dates are entered with an apostrophe in front of them The effect of that is to make them a left justified string. So they are not dates at all.

    You will need to do a find and replace on that cloumn for the apostrophe. I think that will do it.
    David Grugeon
    Brisbane Australia

  8. #8
    New Lounger
    Join Date
    Sep 2006
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format won't change..

    I did notice that on a few but on some of them I didn't see the apostrophe and still it wouldn't change format??

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date format won't change..

    OK I dont think that works but what you can do is to enter a new column with a formula =DATEVALUE(C3) in it ( The C# is a reference to the cell on the same line with the possibly corrupt date in it.)

    This will give you a new column of date numbers. You can copy and paste special values over your dates and away you go!

    You may need to use a formula that tests if the value in C3 is a text or a value and only applies datevalue if it is text My PC is playing up and will not run excel properly so i can't test this. Yuk!
    David Grugeon
    Brisbane Australia

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date format won't change..

    This is the formula to use

    =IF(ISNONTEXT(C15),C15,DATEVALUE(C15))

    It will produce the julian number for the date. you can then apply a date format to this column to see the result.

    Best to use a copy and paste special/values before importing it though.
    David Grugeon
    Brisbane Australia

  11. #11
    New Lounger
    Join Date
    Sep 2006
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format won't change..

    Okay, one last question: how do I cycle through each row in the spreadsheet to run that check? I have about 1500 rows to go through. Thanks for your help. I think I'm almost there!

  12. #12
    Lounger
    Join Date
    Jan 2001
    Location
    Tallinn, Harjumaa, Estonia
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format won't change..

    Just enter the formula at the first row of the table. Then double-click on the small square in the right-bottom corner of the cell. Mention that the mouse-coursor changes to thin cross on the corner. Result is that the formula is copied to each row of the table.

    Anu

Posting Permissions

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