Date Format Mismatch
 

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.

On Fri, 11 Mar 2005 14:46:03 +0500, "Murtaza" <NoEmail@NoEmail wrote:

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.



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

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)


"Ron Rosenfeld" wrote in message
...
On Fri, 11 Mar 2005 14:46:03 +0500, "Murtaza" <NoEmail@NoEmail wrote:

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.



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

On Fri, 11 Mar 2005 11:01:43 -0000, "Bob Phillips"
wrote:

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?

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


--ron

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

Bob


"Ron Rosenfeld" wrote in message
...
On Fri, 11 Mar 2005 11:01:43 -0000, "Bob Phillips"
wrote:

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?

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


--ron

On Fri, 11 Mar 2005 13:58:59 -0000, "Bob Phillips"
wrote:

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

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


--ron