When i import the date format in excel(dd/mm/yyyy) to msaccess it is showing non date value

Question

When I am importing the excel file in date format(dd/mm/yyyy) to access it is showing non date value what is the problem.

eg:27/3/2012 to 33765 here 33765 is a non date value

Answer 1

The reason is because all dates within the MS Office world are actually numeric values, e.g the date you gave above 27/03/2012 equates to 40995.

When using the import wizard to create a new table, Access will automatically read this as it's true numeric value and set the column type as so.

Fortunately it's easy to fix, open up the design view of your table, amend the column in question to a date format and save the table, this should now present all the values correctly.

Alternatively, setup a blank table with the column already set to the date data type and then import your spreadsheet into the existing table.

Answer 2

Access by default thinks that dates coming from Excel are in American date format (mm/dd/yyyy) not European (dd/mm/yyyy).

The number you are seeing is how excel is storing the dates. (every day since 1/1/1990 is +1). When the dates are in european format Access doesnt recognize them as dates during import and may load their raw serial date numbers.

Additionally be sure that the Access column that these are importing to is set as 'date'. I figured that you may have already done that, but I wanted to check.