Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Correct? (2003)

    As part of an annoying project to compile statistics assigned to me by my boss, I stick data in a spreadsheet, and then import it to Access. One of my columns is a date column, and I've decided to format my dates as: 01/2007. However, when I enter this into Excel, it decides I don't know what I'm doing, and "corrects" it to Jan-07. Does anyone know of a way to turn this off? I know you can turn off auto correct options in Word, but I can't find anyplace to do so in Excel.

    TIA for any suggestions--this has been driving me nuts for a while, and I'm finally trying to do something about it!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Correct? (2003)

    If you set the number format of a column to the custom format mm/yyyy before entering data in the column, Excel should respect this and display dates like 03/2007.
    If you enter 03/2007 in a cell set to General format, Excel will apply mmm-yy as format, and I don't think there is a way to avoid that.

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Correct? (2003)

    Thanks for the suggestion. The problem with that solution, is that if I do that when I import the spreadsheet into Access, it changes the format to Date/Time, and mm/dd/yyyy, so then the data is screwed up again. My Excel solution so far has been to enter a date into the column, and then change the column format to text, which it will then respect. If I change it to text first, then it still auto-corrects the field. So, it's annoying to have to enter a date twice, but it's doable if there's no other choice.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Correct? (2003)

    If you pre-format a column as Text, Excel should leave entries such as 03/2007 alone.
    If you don't want to rely on that, you can prefix the entries with an apostrophe '. This tells Excel to treat the entry as text regardless of the format.

    By the way, I don't see the problem with importing the entries as date/time values into Access. You can specify a format there too, in forms and reports based on the table.

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Correct? (2003)

    Yeah, it should leave the entries alone, but I would have sworn it wasn't doing so. However, when I just tested it, it worked fine. Go figure. But, if it starts doing it again, I think putting the apostrophe there will be a quick solution. I had forgotten I could do that. Thanks!

    (The problem with importing the column as date/time values into Access is that then Access changes them to mm/dd/yyyy instead of mm/yyyy. And, I can't use a custom format because I have some rows that contain summary data for the year as just yyyy.)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Correct? (2003)

    If you want to import a worksheet into Access, it shouldn't contain summary rows. You should either calculate the summary info in Access itself, or if that is not possible, put the summary rows in a separate table in the workbook.

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Correct? (2003)

    Sometimes a summary for the year is all the data I have--sometimes it's broken out by month. So, I'm just throwing it all in the same place and then I can run whatever reports I want on the data. Nothing in the table will be looked at without going through a query first, so it's easier for me to just throw it all into one place. That way I can search for all the summary data for 2006, or all the data for Jan 2006, or whatever else I want to do.

  8. #8
    Star Lounger
    Join Date
    Nov 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Correct? (2003)

    Oh, and I think I tried to figure out a custom format for mm/yyyy in Access and couldn't get it to work anyway. But, that was last year sometime, and being a Mommy has run away with my brain since then!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Correct? (2003)

    In itself, mm/yyyy should work in Access, but if you have some values that consist of a year only, they would look very strange. So you're better off with a text field.

Posting Permissions

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