TypeConversionError in dates of Excel .csv document imported to Access

Are there date values in every row? Are there any blanks or possibly spaces? Spaces (which you won’t see) will be a problem. If the problem persists, you can try filter the rows and click the dropdown above your date column to check for inconsistencies.

Hi Bernadette R

From your description, it sounds like the issue is caused by the date column containing a mix of different underlying data types, specifically Excel serial numbers and text-based date formats (such as m/dd/yyyy or mm/dd/yyyy).

Even though they may appear consistent after applying formatting in Excel, the underlying values can still differ. When the file is saved as CSV, those differences are preserved, and Access expects a single consistent data type for each field. This is why the TypeConversionError occurs during import.

To resolve this, you may consider normalizing the dates in Excel before exporting the file. One approach is to create a helper column that converts all values into true Excel date values, for example:

=IF(ISNUMBER(A2), DATE(1900,1,1)+A2-2, DATEVALUE(A2))

After filling this down:

• Copy the new column and paste it as values
• Replace the original column with this cleaned version
• Save the file again as CSV

Once all entries are consistent date values, the import into Access should complete without errors.

Alternatively, if modifying the CSV is not feasible, you could import the column as Text in Access first and then convert it to Date/Time using a query (for example, using CDate).

Please feel free to share any updates from your side, and if I’ve misunderstood anything in your scenario, kindly let me know

And if possible, please share a sample copy of your database file via private message so I can better understand your setup and provide more accurate assistance.

Hope my answer will help you.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Access is failing because the same column in the CSV contains a mix of real Excel dates (the 5‑digit numbers like 43285) and text dates (m/dd/yyyy, mm/dd/yyyy). Access expects one consistent data type per field.

Use this approach:

1. Normalize dates in Excel before exporting
   1. Insert a new helper column next to the existing date column.
   2. In the first row of the helper column, use a formula that converts everything to a real date value, for example (assuming the original date is in A2):

            =DATEVALUE(A2)
      

      If some cells are already 5‑digit serials and others are text, use:

            =DATEVALUE(TEXT(A2,"m/d/yyyy"))
      

   3. Fill this formula down the entire column.
   4. Select the helper column, copy, then use Paste Special → Values to replace formulas with actual date values.
   5. Delete the original mixed-format date column and rename the helper column to the original name.
   6. Format the column as a date in Excel (this is only for display; the key is that the underlying values are all numeric dates).
   7. Save again as CSV.
2. Import into Access with the correct data type
   1. In Access, import the CSV and ensure the date column is set to Date/Time when the import wizard asks for field types.
   2. Complete the import. Because the column now contains only proper date serials, the TypeConversionError should no longer occur.

If the CSV is already created and cannot be changed, import the date column as Text into Access, then create a new Date/Time field in a query using a conversion expression (for example, DATEVALUE([YourTextDateField])) and use that field going forward.

References:

• Move data from Excel to Access
• Import or link to data in an Excel workbook
• Convert dates stored as text to dates