import excel import some wrong date format

[ano]

in excel is total sheet a value copy from original sheet , so no formules any more.
also is cleanup fields not part of functionality.
because there were problems with dates inserting in new table , the column date in excel is customized .
analyze during import created 3 table not clear how he did that so not used.
checking the import as total looks fine.
form1 has buttons create table rate,abc,earn,compare,all
without the format in rate and earn vba several records twist dd-mm
with the format 1 record still lose its time.
seems that excel value sheet hidden format is imported and not the customized.

[Welshgasman]

Date are just that in Excel, Dates as numbers, same as Access.
How they are displayed is up to you, and your Regional settings.

So import the dates AS dates and not text.

? date
30/12/2023 
? cdbl(date)
 45290 
? cdbl(now) 
 45290.4249189815

[ano]

i date the column in excel to make sure all are same.
i create table with date
i change to date during import
the import looks fine
vba to create rate and earn --> several mm/dd -->dd/mm but not all
vba to create rate and earn with format(date,"yyyymmddHHmmss") all seems fine
BUT 1 RECORD has again no time

[Welshgasman]

BUT 1 RECORD has again no time

So there was no time element to start with!

Access does not remove elements from for the fun of it.

Show the excel cell where this errant date exists.
Make a reference to it in another cell and format that cell as having hh:mm after the date in whatever format your country expects.
Then post that as well.

[ano]

i corrected the mistake in the copy.values so the import is clean!
the imported total table looks fine to till u miss records then u find few records with mm/dd --> dd/mm!
but in the copy.values the date column was different date format to make sure all is fine.
the problem come in vba where the total sheet/table are split up into rates,abc,earn there few records got dd/mm again!
after format use in vba insert the dd/mm was solved!!!
only 1 record lost his time again , available in table total!
so what u suggest has no meaning at all bec u focussed on what and why not how to solve
the only way to solve is go in the original sheet and risk not working sheet any more by the many formulas
the problem is the import seems to copy hidden information that is being used by vba from imported access table to new access tables!
gud luck to the ppl who also decide to migrate from excel to access