Date Format (1 Viewer)

Gismo

Registered User.
Local time
Today, 16:12
Joined
Jun 12, 2017
Messages
1,298
Hi,

I requested help from you on Dates in tables and in excel

my requirements change a bit a i need a new approach
i have a spreadsheet with a date format
DDMMYY which is in a text format
i want to import the spreadsheet and immediately write to the table my new date format DD/MM/YY to be able to run different queries from the date field

the format is effeminately in a DDMMYY format and I would like to keep it in the DD/MM/YY format

my problem now is when i run a update query to the table with my date formatted to DD/MM/YY
the date is inverted

Excel - Should look like -
301119 - 30/11/19 - imported as 19/11/30

i tried
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))

but still no luck
 

Minty

AWF VIP
Local time
Today, 14:12
Joined
Jul 26, 2013
Messages
10,354
Take a step back here.

Dates are not stored in the formats you are describing. They are actually stored as decimal numbers.
They are merely displayed in the formats you see based on your regional settings.

In the immediate window type
? Now()

Then type
? CDbl(Now())
? CDblDate())

And see what you get.

So my suggestion would be to convert your text date to an unambiguous format and you won't get caught out. I would use yyyy-mm-dd

Code:
UnAmbiguousDate: IIf(Len([TextDateField_DMMYY])>5,"20" & Right([TextDateField_DMMYY],2) & "-" & Mid([TextDateField_DMMYY],3,2) & "-" & Left([TextDateField_DMMYY],2),"20" & Right([TextDateField_DMMYY],2) & "-" & Mid([TextDateField_DMMYY],2,2) & "-" & "0" & Left([TextDateField_DMMYY],1))

Edit : Noticed an error
Replace [TextDateField_DMMYY] with your date field. The above assumes no dates prior to 2000, if you do it gets a tad more interesting.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:12
Joined
May 7, 2009
Messages
19,169
change your Computer (Regional setting) date format to dd/mm/yy.
and import as normal without any format.
 

Gismo

Registered User.
Local time
Today, 16:12
Joined
Jun 12, 2017
Messages
1,298
change your Computer (Regional setting) date format to dd/mm/yy.
and import as normal without any format.
I would have to change all the regional settings on all computers using the import, correct?
 

Gismo

Registered User.
Local time
Today, 16:12
Joined
Jun 12, 2017
Messages
1,298
Take a step back here.

Dates are not stored in the formats you are describing. They are actually stored as decimal numbers.
They are merely displayed in the formats you see based on your regional settings.

In the immediate window type
? Now()

Then type
? CDbl(Now())
? CDblDate())

And see what you get.

So my suggestion would be to convert your text date to an unambiguous format and you won't get caught out. I would use yyyy-mm-dd

Code:
UnAmbiguousDate: IIf(Len([TextDateField_DMMYY])>5,"20" & Right([TextDateField_DMMYY],2) & "-" & Mid([TextDateField_DMMYY],3,2) & "-" & Left([TextDateField_DMMYY],2),"20" & Right([TextDateField_DMMYY],2) & "-" & Mid([TextDateField_DMMYY],2,2) & "-" & "0" & Left([TextDateField_DMMYY],1))

Edit : Noticed an error
Replace [TextDateField_DMMYY] with your date field. The above assumes no dates prior to 2000, if you do it gets a tad more interesting.
my regional setting is ddmmyy
1604992783270.png


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

will I be able to do calculation with the text field such as to add 12 month to the value?

how do i add 12 months to text in a format of dd/mm/yy

220220 - DDMMYY - 22/02/20
+12 Months should be 22/02/21 (Text)
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:12
Joined
May 7, 2009
Messages
19,169
i guess so,

you can also try without changing the regional setting:


Expr1: DateSerial(Val(Right([Installation Date (DDMMYY)],2))+2000,Val(Mid(Installation Date (DDMMYY)],3,2)),Val(Left(Installation Date (DDMMYY)],2)))
 

Minty

AWF VIP
Local time
Today, 14:12
Joined
Jul 26, 2013
Messages
10,354
my regional setting is ddmmyy
View attachment 86503

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

will I be able to do calculation with the text field such as to add 12 month to the value?

how do i add 12 months to text in a format of dd/mm/yy

220220 - DDMMYY - 22/02/20
+12 Months should be 22/02/21 (Text)
I would strongly advise against doing this.
A date should be stored as a date, regardless of the presentation required.
By storing it as text data, you will get all sorts of issues, including not being able to do simple comparisons and additions.

On a form (forget the table) you can force a display to anything you like, but if it is stored as a date it will present correctly for your local regional settings. And anyone else if their settings are different. That won't happen with a text field.
 

Gismo

Registered User.
Local time
Today, 16:12
Joined
Jun 12, 2017
Messages
1,298
so
I would strongly advise against doing this.
A date should be stored as a date, regardless of the presentation required.
By storing it as text data, you will get all sorts of issues, including not being able to do simple comparisons and additions.

On a form (forget the table) you can force a display to anything you like, but if it is stored as a date it will present correctly for your local regional settings. And anyone else if their settings are different. That won't happen with a text field.
, will it then be advised to take my text data from excel and re arrange DDMMYY to text yymmdd then to a date format yymmdd in access then to calculations
 

Minty

AWF VIP
Local time
Today, 14:12
Joined
Jul 26, 2013
Messages
10,354
If you store the excel data after using the YYYY-MM-DD formatting as a date it will be correct in access.
So today 10/11/2020 UK style will be stored as a value of 44145 in access.
In the US that same value would be displayed as 11/10/2020

You must stop thinking about date display formats as storage formats. A date is only ever stored as a decimal number.
They generally take care of themselves.

The only time you need to worry about it is when doing any direct SQL manipulation in code.
In that case, you must use the US or preferably YYYY-MM-DD formats to avoid Access misinterpreting things.

Read up here for some further information http://allenbrowne.com/ser-36.html
 

Gismo

Registered User.
Local time
Today, 16:12
Joined
Jun 12, 2017
Messages
1,298
Thank you
If you store the excel data after using the YYYY-MM-DD formatting as a date it will be correct in access.
So today 10/11/2020 UK style will be stored as a value of 44145 in access.
In the US that same value would be displayed as 11/10/2020

You must stop thinking about date display formats as storage formats. A date is only ever stored as a decimal number.
They generally take care of themselves.

The only time you need to worry about it is when doing any direct SQL manipulation in code.
In that case, you must use the US or preferably YYYY-MM-DD formats to avoid Access misinterpreting things.

Read up here for some further information http://allenbrowne.com/ser-36.html
very much Minty,

let me play around with you suggestions and thank you for the info, much appreciated

just a quick question, if 220120 is a number representing ddmmyy 22/01/20,
adding 12 months or 265 days
DateAdd("d",[Days],[Installation Date])
result is 2503/08/30
which should be 22/01/21
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 14:12
Joined
Sep 21, 2011
Messages
14,042
? cdate(220120)
31/08/2502
 

Minty

AWF VIP
Local time
Today, 14:12
Joined
Jul 26, 2013
Messages
10,354
Thank you

very much Minty,

let me play around with you suggestions and thank you for the info, much appreciated

just a quick question, if 220120 is a number representing ddmmyy 22/01/20,
adding 12 months or 265 days
DateAdd("d",[Days],[Installation Date])
result is 2503/08/30
which should be 22/01/21
That won't work as @Gasman has pointed out.
As a number 220120 is meaningless as a representation of the date it infers.

That is why you need to convert it to a meaningful date, then do your calculations.
 

Gismo

Registered User.
Local time
Today, 16:12
Joined
Jun 12, 2017
Messages
1,298
That won't work as @Gasman has pointed out.
As a number 220120 is meaningless as a representation of the date it infers.

That is why you need to convert it to a meaningful date, then do your calculations.
okey, seems like i am totally lost
if 220120 is a number which should represent 22/01/20 (ddmmyy) at the end of the day
how do i add 365 days to this number to represent 22/01/21 in my final report
as you mentioned, 220120 is meaningless, how do I convert this number in to a meaningful date format

i understand that you say that i should not think of it as a date format, most probably that which is throwing me off the bridge here
 

Minty

AWF VIP
Local time
Today, 14:12
Joined
Jul 26, 2013
Messages
10,354
Use the functions we have already described earlier in the thread.

Are you working directly with the Excel data or importing it to your own tables?
If the latter (which I would recommend) then simply add a field called ActualDate or similar and run an update query to set it's value to the date returned by the functions above.

Then MyNewDate: DateAdd("d",365,[ActualDate]) would achieve your result.
 

Gismo

Registered User.
Local time
Today, 16:12
Joined
Jun 12, 2017
Messages
1,298
Use the functions we have already described earlier in the thread.

Are you working directly with the Excel data or importing it to your own tables?
If the latter (which I would recommend) then simply add a field called ActualDate or similar and run an update query to set it's value to the date returned by the functions above.

Then MyNewDate: DateAdd("d",365,[ActualDate]) would achieve your result.
i am importing directly in to access table then i do the alterations
i have a new field there i want to update the formatted date

import field is a number format
my new field is a date format

i have used the DateAdd function as described in post 10

220120 is a number representing ddmmyy 22/01/20,
adding 12 months or 265 days
DateAdd("d",[Days],[Installation Date])
result is 2503/08/30
which should be 22/01/21

i need the number field to be converted into a new field which is in a date format
i need to do run different calculations on this field
some might be to add 12 month, some add 2 years and some add days

i have converted all to days to make it easier to update in one singe format

so i need the actual number in a date format in a new field and then in a 3rd date field, i will do the individual date calculations
1 - number date (Installation Date)
2 - date format (Installation Date)
3 - Date format with calculation for Removal date
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:12
Joined
Sep 21, 2011
Messages
14,042
I have to ask?
Why can they not have an actual date in the excel worksheet.?
 

Gismo

Registered User.
Local time
Today, 16:12
Joined
Jun 12, 2017
Messages
1,298
I have to ask?
Why can they not have an actual date in the excel worksheet.?
this is the format downloaded from the maintenance program
i have to download one file per aircraft
then import one by one into access

there is absolutely no reason why i can not have the date in excel
suppose i could try this the other way around to convert excel to a date then import into access
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:12
Joined
Sep 21, 2011
Messages
14,042
this is the format downloaded from the maintenance program
i have to download one file per aircraft
then import one by one into access

there is absolutely no reason why i can not have the date in excel
suppose i could try this the other way around to convert excel to a date then import into access
I'd certainly try that.
 

Users who are viewing this thread

Top Bottom