How to change field format in access?

[Chris Clavius]

I have exported data from access 2003 table to excel where my date field was not recognized as date. After looking closely to my table I then realized that I didn't set the field's format to any of the date type. I tried to import the table structure and changed the format to yyyy/mm/. I then appended the data to my new table but still didn't solve the problem. Please help me to solve this.

[munkee]

I am unsure whether this will help too much but I know when working in excel dates and numerics can be hard to manipulate if infact they are being stored as "text" and not true dates.

Dates are stored in excel as a numerical value such as 402600 these are then altered to the nice date format of dd/mm/yyyy etc such as 12/02/1986 using cell formatting. It may be the case that access is also coming up against this issue when you are trying to put the data back.

[Lysander]

I have always had problems with date fields. The first problem is that a date field is stored as a number. I forget what 0 is, but some significant day in the 1800's. Anyways, the number is different in Excel than it is in Access, well it was in Office 2000.

Also, date formats change across the world, and day and month can get swithed around.

I now store all dates as 3 numbers, day,month,year which means I can handle any date, convert from access to Excel and back, and not have to worry about UK dates being converted to USA dates.

[NeoPa]

Munkee:
these are then altered to the nice date format of

That's a helpful response, but be careful. Date values are not altered at all. They are simply displayed differently. This may seem an unimportant point, but if you think about it, this confusion is the cause of most misunderstandings that relate to dates and times.

If you remember that dates are stored as dates (numbers exactly as Munkee has explained) and only ever appear as text when they are formatted for display, then they become much easier to work with without issue.

The rule of thumb is always to work with data in its native format, and only ever convert it to text for the purposes of display or transfer, and with a transfer, only when necessary and the other end understands to convert it back from text before loading the data.

PS. I wanted to add, to be absolutely clear, that this is not intended as a criticism of Munkee's post, which is very helpful. It is merely to make a very important point about that one critical issue.