export to excel-date problem

[diegomarino]

Is it not the first time i had this problem, but why if i export my table in excel, then link the excel in a linked table, all the dates are messed up?
in some cases dates are the same in excel and access but the query consider them different, and i can fix it by clicking on the excel celles (one by one), the date value stay the same but when i make the query in access it works, and the program can find the match


this is so weird

[ranman256]

How is the date messed up? is it not a date?

Excel will 'guess' at what's in the field. If the 1st few fields dont look like dates, then it will assign them a different datatype.
same if a field has both numbers and strings, so it guesses that the field is numeric, then the string data will show as #Error, so you must 'hard wire' the field with a leading single quote to force it as string.

so make sure your date values are actually date values.

[diegomarino]

they are for sure. in my real database happens a very strange thing: the date is the same (e.g. 13/04/2021 11:00:00), but the query consider it as different values. If i click in the cells of excel file that row do not change the date but it "heals" the value and the query recognize it as same value. could be some different setting of date/time? it is so strange, only a bunch of tables have this issue

[diegomarino]

i solved it using a power query in escel instead of exporting
that's a mistery that will never be solved
thanks

[Gicu]

The issue might be caused by the difference between the reference first date between Access and Excel date systems. Access dates start on December 30, 1899 and Excel has either January 1, 1900 or January 1, 1904. The question is why clicking the cell "fixes" it.

https://sfmagazine.com/post-entry/ju...2031%2C%201899.

https://docs.microsoft.com/en-us/off...04-date-system

[diegomarino]

i knew it was some strange reason. Now i'm aware of it, thanks

[Micron]

The question is why clicking the cell "fixes" it.

Maybe because that exposes the cell value rather than what the format is? If so, it's interesting that it doesn't revert back to the format when the cell loses focus. Or does it?

[diegomarino]

it doesn't, it "heals" the cell
however in naples we say "attacc o ciucc a ro va o padron" that means "tie the donkey where the master wants", so i just do what excel wants and question no more