Date Format (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 01:40
Joined
Sep 21, 2011
Messages
7,500
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

AWF VIP
Local time
Today, 01:40
Joined
Jul 26, 2013
Messages
7,798
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

Registered User.
Local time
Today, 03:40
Joined
Jun 12, 2017
Messages
685
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
1605014840152.png



all the data seems to be correct
 

Gismo

Registered User.
Local time
Today, 03:40
Joined
Jun 12, 2017
Messages
685
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

1605014998978.png


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

1605015075708.png
 

Minty

AWF VIP
Local time
Today, 01:40
Joined
Jul 26, 2013
Messages
7,798
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

Enthusiastic Amateur
Local time
Today, 01:40
Joined
Sep 21, 2011
Messages
7,500
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

Immoderate Moderator
Staff member
Local time
Yesterday, 19:40
Joined
Feb 28, 2001
Messages
18,747
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:40
Joined
Feb 19, 2002
Messages
30,665
Just FYI, Dates in COBOL were strings. Typically - yyyymmdd but yymmdd and yyddd were also common. The applications that used two digit or 1 digit years were the ones that caused the Y2K issues which pundits thought would cause planes to fall out of the sky when the date changed from 12/31/1999 to 1/1/2000. But if it didn't happen then, it would surely happened when the date went from 2/28/2000 to 3/1/2000 skipping 2/29/2000 because 2000 was a leap year :).

i removed the data type as Date and changed it to a text field, same as in the excel spread sheet as this is in the format I need
You really need to store the field as a date. Getting from the string in the spreadsheet to a date is the issue. The problem is easily solved and the solution has already been given to you I'm sure. LINK to the spreadsheet. DO NOT import it. Then in your APPEND query, you can reformat the string date into a valid date format - at this stage go with yyyy/mm/dd since that is NOT ambiguous and will get the data stored correctly regardless of your internal date settings.

Once you have a date stored correctly, who cares how you want to format it. There are lots of threads here discussing the problems that arise when your default date format is dd/mm/yy. Sorry, about that but SQL defaults to mm/dd/yy as the default and assumes that for any string date. Once you get the date into a DATE datatype field, it will always operate correctly because it will be a double precision number. Dec 30, 1899 = 0. Dates earlier than that are negative and dates after that are positive. So Dec 29, 1899 = -1 and Dec 31, 1899 = 1. Time is a decimal value and ALL dates include time so when you populate a date field use Date() if you want to ignore time and Now() where you want to include the current time.
 
Last edited:

Gismo

Registered User.
Local time
Today, 03:40
Joined
Jun 12, 2017
Messages
685
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
 

Attachments

  • ZS-HCN - Object - Copy.zip
    15.4 KB · Views: 10

Gismo

Registered User.
Local time
Today, 03:40
Joined
Jun 12, 2017
Messages
685
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

AWF VIP
Local time
Today, 01:40
Joined
Jul 26, 2013
Messages
7,798
Glad you sorted it out. Sometimes we learn a lot more from not getting it right immediately.
 
Last edited:

Users who are viewing this thread

Top Bottom