Auto Correct? (2003)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote