Date Format Mismatch

M

Murtaza

I need to accumulate data from several countries with different date-format
than US. Like some send us the .txt files with following data:

-------File1.txt-----------------------------
Actual Data: 1/1/2004 Account Open
Translation: 1/Jan/2004

Actual Data: 8/12/2004 Account Closed
Translation: 8/Dec/2004
--------------------------------------------

-------File2.txt-----------------------------
Actual Data: 1/1/2004 Account Open
Translation: 1/Jan/2004

Actual Data: 8/12/2004 Account Closed
Translation: 12/Aug/2004
* as per their regional date settings

Actual Data: 8/13/2004 Account Closed
Translation: 13/Aug/2004
* as per their regional date settings

--------------------------------------------

Now If I import / copied the data of both files into Excel, MS Excel using
my date regional settings tanslate both dates in d/m/yyyy so i get only one
date i.e. 8/Sept/2004.

Even manually its hard to guess that which date-format is used in File2, but
after looking at series of dates one can found what date format has been
used.
 
R

Ron Rosenfeld

I need to accumulate data from several countries with different date-format
than US. Like some send us the .txt files with following data:

-------File1.txt-----------------------------
Actual Data: 1/1/2004 Account Open
Translation: 1/Jan/2004

Actual Data: 8/12/2004 Account Closed
Translation: 8/Dec/2004
--------------------------------------------

-------File2.txt-----------------------------
Actual Data: 1/1/2004 Account Open
Translation: 1/Jan/2004

Actual Data: 8/12/2004 Account Closed
Translation: 12/Aug/2004
* as per their regional date settings

Actual Data: 8/13/2004 Account Closed
Translation: 13/Aug/2004
* as per their regional date settings

--------------------------------------------

Now If I import / copied the data of both files into Excel, MS Excel using
my date regional settings tanslate both dates in d/m/yyyy so i get only one
date i.e. 8/Sept/2004.

Even manually its hard to guess that which date-format is used in File2, but
after looking at series of dates one can found what date format has been
used.
Click to expand...

If you are importing/opening *.txt files, the convert Text To Columns wizard
should open up. In that wizard, you have the opportunity to specify the MDY
configuration of your data.


--ron
 
B

Bob Phillips

Could you not get every country to conform to a date standard, say
yyyy-mm-dd, then it is no one imposing on the other?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

Ron Rosenfeld

Could you not get every country to conform to a date standard, say
yyyy-mm-dd, then it is no one imposing on the other?
Click to expand...

Do you think that will happen before or after we get world peace? :))


--ron
 
B

Bob Phillips

Lol. Within his own organisation one could hope for some process to make the
wheels turn, couldn't we?

Bob
 
R

Ron Rosenfeld

Lol. Within his own organisation one could hope for some process to make the
wheels turn, couldn't we?
Click to expand...

Oh, I thought you were talking about countries :). Maybe he won't have to
wait for world peace!


--ron
 
Top