excel date format import bug

[ryanm]

Dear Microsoft groups,

We have found that attempting to import a dd/mm/yyyy format column from
a text file does not work in Mac Office 2004 Excel (latest greatest
updates/servicepacks/patches etc) using OSX 10.4.4

No matter what we do, or pull down option we select (e.g. dd/mm/yy
format selected import option as opposed to mm/dd/yy default) we always
get mm/dd/yy.


e.g. the text file has:

6 12 2005

We select import column as dd/mm/yy

We get a column showing: June 12th 2005

What we should get is Dec 6 2005

Interestingly the exact same text file using the same import selected
options, importing works on Windows XP with Office XP and yields Dec 6
2005.

This appears to be a Mac Office 2004 issue with Excel? Could it be the
classic end-of-line/paragraph mark difference with Mac/PC/Unix files?

Any suggestions on this would be extremely helpful.

Thanks,
Ryan

 

[JE McGimpsey]

Dear Microsoft groups,

We have found that attempting to import a dd/mm/yyyy format column from
a text file does not work in Mac Office 2004 Excel (latest greatest
updates/servicepacks/patches etc) using OSX 10.4.4

No matter what we do, or pull down option we select (e.g. dd/mm/yy
format selected import option as opposed to mm/dd/yy default) we always
get mm/dd/yy.


e.g. the text file has:

6 12 2005

We select import column as dd/mm/yy

We get a column showing: June 12th 2005

What we should get is Dec 6 2005

Interestingly the exact same text file using the same import selected
options, importing works on Windows XP with Office XP and yields Dec 6
2005.

This appears to be a Mac Office 2004 issue with Excel? Could it be the
classic end-of-line/paragraph mark difference with Mac/PC/Unix files?

Don't see how the EOL character would make a difference, but you could
check by changing the File Origin dropdown in the text file import
wizard.

When I import a .txt file containing "6 12 2005", and choose d/m/y from
the date dropdown in the third pane of the import wizard, I get 6
December 2005.

 

[ryanm]

Good morning,

I double checked. Our data file imports as mm/dd/yy no matter what we
do with it in terms of import options.

The original file looked like this:

"08/12/2005 6:57:50 PM",67.7,3.3,2
"08/12/2005 7:00:50 PM",67.7,3.3,3
"08/12/2005 7:03:50 PM",67.8,3.1,4
"08/12/2005 7:06:50 PM",67.7,3.2,5

I found that removing the " didn't help, but if I removed the
hour:min:sec time portion then the dd/mm/yy import worked.

For example:

08/12/2005,67.8,3.3,0
08/12/2005 6:54:50 PM,67.8,3.3,1
"08/12/2005 6:57:50 PM",67.7,3.3,2
"08/12/2005 7:00:50 PM",67.7,3.3,3
"08/12/2005 7:03:50 PM",67.8,3.1,4

Shows Dec.12.2005 for the first data point, but Aug 12.2005 for each
subsequent point.

Any idea (since we need the hh:mm:sec part) how we could import this?

Remember that the PC version of Office XP imported no problem.

Thanks,
Ryan

 

[Bob Greenblatt]

Good morning,

I double checked. Our data file imports as mm/dd/yy no matter what we
do with it in terms of import options.

The original file looked like this:

"08/12/2005 6:57:50 PM",67.7,3.3,2
"08/12/2005 7:00:50 PM",67.7,3.3,3
"08/12/2005 7:03:50 PM",67.8,3.1,4
"08/12/2005 7:06:50 PM",67.7,3.2,5

I found that removing the " didn't help, but if I removed the
hour:min:sec time portion then the dd/mm/yy import worked.

For example:

08/12/2005,67.8,3.3,0
08/12/2005 6:54:50 PM,67.8,3.3,1
"08/12/2005 6:57:50 PM",67.7,3.3,2
"08/12/2005 7:00:50 PM",67.7,3.3,3
"08/12/2005 7:03:50 PM",67.8,3.1,4

Shows Dec.12.2005 for the first data point, but Aug 12.2005 for each
subsequent point.

Any idea (since we need the hh:mm:sec part) how we could import this?

Remember that the PC version of Office XP imported no problem.

Thanks,
Ryan

Yes, looks like a bug. I'll report it.