Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to format a date to a different format | Excel Discussion (Misc queries) | |||
Date Format Question | Excel Discussion (Misc queries) | |||
USING THE DATE FORMAT IN EXCEL | Excel Discussion (Misc queries) | |||
problem with formatting cell to date format | Excel Worksheet Functions | |||
Problem with Date format from VBA code | Excel Discussion (Misc queries) |