We have given you a perfectly good way to convert your original number data to the correct date format for Access.
All you need is to perform the maths on the converted new date field in the Access Table, not your original Excel number.
Am I missing something here?
i seem to miss a few thing on my side as well as i tried all of you suggestions
so what i have done now is
import from excel
into number format 310318 (representing ddmmyy)
then using below, i copy to a new field as a date format but with opposite date format yy/mm/dd)
Expr1: IIf(Len([Installation Date (DDMMYY)])>5,Left([Installation Date (DDMMYY)],2) & "/" & Mid([Installation Date (DDMMYY)],3,2) & "/" & Right([Installation Date (DDMMYY)],2),"0" & Left([Installation Date (DDMMYY)],1) & "/" & Mid([Installation Date (DDMMYY)],2,2) & "/" & Right([Installation Date (DDMMYY)],2))
so the date format is opposite to what I need but the display is correct 31/03/18
format is yy/mm/dd but display is dd/mm/yy
can i work with this for calculations?
even thou i get an error
all the data seems to be correct