Does anyone know why this date format is used.

[Mike375]

I just received an Excel file from an insurance company listing all client details and one of those is date of bith.

19560130
19511005

I am in Australia so UK type date. The first one is January 30 1956 and the next os October 5 1951. 30/01/1956 and 05/10/1951

The dates/numbers are aligned to the right. If add text like xxx19560130
then the entry jumps to the left.

I am just curious why the data would come this way. Would this be related to how the insurance company's data base exports to Excel.

For other companies that send the data in Excel they are in the normal Australian format.

 

[chergh]

I believe that is commonly known as 'international standard date notation' as defined by ISO 8601.

 

[Mike375]

I believe that is commonly known as 'international standard date notation' as defined by ISO 8601.

Interesting. So would there be some type of function that would change it to 30/01/1956 . No big deal as it easy for me to change it to that in Access but again, just curious.

 

[namliam]

Yes ISO date... YYYYMMDD or YYYY-MM-DD

This is a date format where
1) the number is nevery causing a problem... i.e. 5/10 is that May 10 or Oct 5?
2) You can use the number to get a proper sorting.

In pretty much any database you can pick and choose what format you want your dates in.

Note:
Using excel for data transfer purposes is not the best thing there is... This can cause multiple problems in seemingly random situations. Data transfers are best done in Text files, and yes... also dates in non-local formats even if inside a country. My dutch brain still has trouble ready 5/10 as May 10, eventhough I have worked for English/American companies most my addult live.
Instead I will allways go for Oct 5.

Same goes for time (use 24 hour preferably) and numbers (no thousands seperators anywhere)

 

[namliam]

So would there be some type of function that would change it to 30/01/1956 .

Nope unfortunatly....

1956-01-30, using CDate will convert it properly...

However 19560130 you will have to use Mid, left and right, then use Dateserial to make it a date (or any other date conversion function like CDate as long as you put it together right.... Dateserial is the safest method IMHO)

Good luck....

 

[chergh]

English companies use dd/mm as well, think mm/dd is a yank thing.

Most confusing way I ever saw date and time written was like:

1227788005

Thats today in epoch time, took me a while to figure out what it was.

 

[namliam]

I think unix time stamps show a number of SECONDS passed since IIRC 1/1/1970

What ever the setting used by my employer / Customer, dd-mm-yyyy is it for me... Unless in ISO yyyy-mm-dd.
I have even had files delivered to me in the CRAZY yyyy-dd-mm format (no clue why???)

Still as long as you know how to convert any given string into something usefull.... I dont see the problem.
i.e.
72110280

Now that looks whacky, but just reverse every 2 characters and you get 27112008, which is seemingly obvious.

 

[Mike375]

Note:
Using excel for data transfer purposes is not the best thing there is... This can cause multiple problems in seemingly random situations. Data transfers are best done in Text files, and yes... also dates in non-local formats even if inside a country. My dutch brain still has trouble ready 5/10 as May 10, eventhough I have worked for English/American companies most my addult live.
Instead I will allways go for Oct 5.

Same goes for time (use 24 hour preferably) and numbers (no thousands seperators anywhere)

No choice. Most do Excel and some send Access tables. But there are no problems and probably because we are very familiar with the data or the person we do it for is very familiar.

Of course where Australian/UK and American dates can be confusing is when both day and month a 12 or less

As a side note, in an non English speaking country to use the Dutch words for various properties. For example, this Item from a SetValue macro

[Forms]![2ProspectT]![Label1895].[ForeColor]

Do you use ForeColor. Actually, if there was an Australianised or English version that would become

[Forms]![2ProspectT]![Label1895].[ForeColour]

How do Arabs, Hebrews and Asians go?

 

[chergh]

You can use this to convert it if you want

Public Sub TimeConv(rng As Range)

rng.Offset(0, 1).Value = CDate(Right(rng.Value, 2) & "/" & Mid(rng.Value, 5, 2) & "/" & Left(rng.Value, 4))


End Sub

 

[namliam]

All programming and form stuff is international, or not??

Queries require US format dates!
Color not Colour
etc, I expect it to be the same elsewhere.

I even (still) go for a loop (for a second) on 5/15/2008....
The problems start comming when (newer) people work with dutch dates, while access sees US dates. There is a question to that extend on these forums just about once a week if not more.

 

[namliam]

CDate(Right(rng.Value, 2) & "/" & Mid(rng.Value, 5, 2) & "/" & Left(rng.Value, 4))

Nooooooooooooooooooooooooooooooooooooooooooooooooooo ! No no no, please dont fall for the CDate trap!

CDate is "regional settings" dependant....
Settings to Dutch:
?Format(CDate("05/10/2008"),"dd-MMM-YYYY")
05-okt-2008 (okt = Oct)
Settings to US:
?Format(CDate("05/10/2008"),"dd-MMM-YYYY")
10-May-2008

This will and has caused many a problem here in the NL, just because someone had different settings....
Dateserial is the 'only' save way....

Or maybe use CDate, but use it in an ISO format
Dutch:
?Format(CDate("2008-10-05"),"dd-MMM-YYYY")
05-okt-2008
US:
?Format(CDate("2008-10-05"),"dd-MMM-YYYY")
05-Oct-2008

 

[Mike375]

Queries require US format dates!

Not here.

>#30/03/1958# would get all the dates after the 30th March 1958

 

[chergh]

Yeah it's only in VB editor that I find you have to use US dates when hardcoding dates in the access query designer local date formats work fine, though date serial would probably a good habit for me to get into.

 

[namliam]

Not here.

>#30/03/1958# would get all the dates after the 30th March 1958

That is because month 30 dont exist, it "magicaly" works, now try 10/03/1958

Edit:
Guess it shows I hardly ever use dutch settings.... Yes, in the designer the local times work... which offcourse with my local settings set to US....

@Chergh
Dateserial or ISO, or both... definatly a "good practice" kind of thing.

 

[Mike375]

You can use this to convert it if you want

Public Sub TimeConv(rng As Range)
 
rng.Offset(0, 1).Value = CDate(Right(rng.Value, 2) & "/" & Mid(rng.Value, 5, 2) & "/" & Left(rng.Value, 4))
 
 
End Sub

I did it in Access

FixDOB: Right([Field4],2) & "" & "/" & "" & Mid([Field4],5,2) & "" & "/" & "" & Left([Field4],4)

But I was whether there was some standard function.

 

[namliam]

No standard function. Plus your "new date" isnt really a date yet.... you need to use some conversion function to make it a date (CDate, Dateserial, Datevalue)

Or if you are storing it in a column that is a date field... You are then implicitely converting string to date, doing anything implicit (allowing access to think for you) is not advicable... again Regional settings and stuff like access programming come into play for which you dont know 100% what it does and dont have 100% that it will stay the same.
If you constantly use implicit conversions, one day one will come around to haunt you!
Convert Explicitely! Use convert functions! They are there for a reason!

Side note...
Why the extra "" in there ?

FixDOB: Right([Field4],2) & "" & "/" & "" & Mid([Field4],5,2) & "" & "/" & "" & Left([Field4],4)

Why the bolded parts??

 

[chergh]

Nah if your converting date and times it's usually build your own function time, or google it if it's not something you can do off the top of your head.

 

[Mike375]

Side note...
Why the extra "" in there ?

FixDOB: Right([Field4],2) & "" & "/" & "" & Mid([Field4],5,2) & "" & "/" & "" & Left([Field4],4)

Why the bolded parts??

Just habit and having then all over the place and often just copy/paste and make a change.

Just to stop your eyes hurting and for you only

Right([Field4],2) & "/" & Mid([Field4],5,2) & "/" & Left([Field4],4)

 

[namliam]

Was just curious about the need for it, no need to change it on my (eyes') behalve.

Nothing in response to the Implicit part of the post?
If you were to change anything on my behalve it would be to NEVER use an implicit conversion again.

 

[Mike375]

No standard function. Plus your "new date" isnt really a date yet.... you need to use some conversion function to make it a date (CDate, Dateserial, Datevalue)

What about when it is appended to a date field.

As a side note, Austalian life insurance companies have the "policy number" as the client. Thuis if John Smith has two policies numbers then he will be in the One table twice. The various policy benefits that are on the policy are in the Many table. I change that so John Smith is there only once, which causes me a little fiddling when a data base is first set up but OK from then on.