Date Format

[Gasman]

What do you get when the day is under 10? do you get a leading zero.
Plus that is coing in as yymmdd not what you say it is?
Put day(),Month() and year() functions into another column and see what Excel thinks they are in that sheet.

 

[Minty]

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?

 

[Gismo]

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

 

[Gismo]

What do you get when the day is under 10? do you get a leading zero.
Plus that is coing in as yymmdd not what you say it is?
Put day(),Month() and year() functions into another column and see what Excel thinks they are in that sheet.

the data from excel is indeed dd/mm/yy

below from excel

below from report, we installed this part last year end November so i can confirm format is dd/mm/yy

 

[Minty]

It might look like a date, but I suspect it is not based on how it exports into Access.

Also, I suggested using YYYY-MM-DD not yy/mm/dd and provided an expression that will do that.
yy/mm/dd is still ambiguous.

Write a simple select query with the original value and then your calculated value - surround the whole thing with DateValue( the expression) and see what it returns.

If you can upload some of your original data as it is in the spreadsheet we could possibly confirm some of our guesses?
Remove any confidential data.

 

[Gasman]

the data from excel is indeed dd/mm/yy

below from excel

View attachment 86511

below from report, we installed this part last year end November so i can confirm format is dd/mm/yy

View attachment 86512

For all you know, that could be in yymmdd storage and just formatted as what you see.?, just as you can in Access and Excel and other applications?
I seem to recall that COBOL would store dates in YYYYMMDD ?

Your manipulations also seem to indicate this.?
If you used the 3 functions I mentioned that would also confirm it.?

 

[The_Doc_Man]

And there you find the ultimate ambiguity. You have a variant of the Y2K problem. If you have 211119, Did you intend that to mean 19-Nov-2021 or 19-Nov-1921? Or was that 21-Nov-2019 or 21-Nov-1919? All are valid dates in that both are greater than the Access or Excel "Epoch Date" (internal reference date), and one could imagine that for some reason you could have something 99 years old in your system rather than dealing with something this time next year. This is why we say "convert date-text to real dates." No ambiguity once converted.

This is why a text representation is useless in manipulations. If you had a date field, DateAdd would work just great first time, every time. There are perhaps a dozen ways to do this conversion - but it starts by defining the conversion process that is right for the data you have. First, recognize that you have a Y2K problem based on a 2-digit year. So pick this apart as 3 x 2-digit strings. Then concatenate either 19 or 20 to the year-string as appropriate. ArnelGP's DateSerial solution listed earlier probably would work just as well as any other.

 

[Gismo]

It might look like a date, but I suspect it is not based on how it exports into Access.

Also, I suggested using YYYY-MM-DD not yy/mm/dd and provided an expression that will do that.
yy/mm/dd is still ambiguous.

Write a simple select query with the original value and then your calculated value - surround the whole thing with DateValue( the expression) and see what it returns.

If you can upload some of your original data as it is in the spreadsheet we could possibly confirm some of our guesses?
Remove any confidential data.

attached is the excel file
in yellow the field in question

 

[Gismo]

I managed to get my date sorted
i used the advise you gave but took me on quite a journey but i learned quite a bit

i created a new field in by tbl
changed the format from dd/mm/yy to yy/mm/dd but with errors
then found that YYYY/mm/dd worked as you mentioned

thank you so much for your assistance and give some advise and training on how dates work

 

[Minty]

Glad you sorted it out. Sometimes we learn a lot more from not getting it right immediately.