Date Format Mismatch

  • Thread starter Thread starter Murtaza
  • Start date Start date
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.
 
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
 
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)
 
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
 
Lol. Within his own organisation one could hope for some process to make the
wheels turn, couldn't we?

Bob
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

LOOKUP function between two dates in Excel 2007 6
Date Formatting & Sorting 2
Dates in Excel 4
Pivot Table/Chart Grouping 4
Avg/date 2
Calculations within summary cells, reflecting the visble cells only after filtering 5
Another Date format oddity 4
Extracting time (hr:min) from date/time format 2

Back
Top