Date formats converting when docs sent to Japan

[Guest]

Hello,
Not sure if anyone can help, but we are experiencing a problem with dates in
Excel when the documents are sent to Japan. For example, I pass them dates
and the dates (02/03/04) are being read as 2002/03/04 instead of 02/03/2004.
Any thoughts on how this can be fixed?

 

[David Biddulph]

It sounds as if the date wasn't actually being stored as a date, but as
text, and the text was in such a form as to be ambiguous. For the future,
store it as a date, and preferably format it unambiguously. [For your
02/03/2004, make it 02 Mar 2004 or Feb 03 2004, for example.]

For your existing data which has been misinterpreted, try Data/ Text to
columns/ delimited/ out to the final stage then specify Column data format
as Date, & specify DMY or MDY as appropriate.

 

[Guest]

The problem we're having is when the end user dumps data out of a canned
reporting tool (a querying tool that comes with our HR Database - ADP) into
Excel. The canned reporting tool provides dates in the mm/dd/yy format, and
as soon as she opens it up in Excel, the dates are EITHER formatted to a
yyyy/mm/dd (or perhaps yyyy/dd/mm - not sure which) or left alone if it can't
reformat them.

For example:

09/01/04 gets converted to: 2009/1/4
11/01/00 remains the same: 11/01/00

I think Excel TRIES to convert the date fields to its yyyy/mm/dd format but,
if it can't (i.e.: there is no day 00, so 11/01/00 stays the same) it leaves
the field alone. What I'm ending up with is a report that has half the dates
remaining in the original format (likely a text format) and the other half
being converted to the WRONG date. I'm stuck.

--
Thank you! - Jennifer

It sounds as if the date wasn't actually being stored as a date, but as
text, and the text was in such a form as to be ambiguous. For the future,
store it as a date, and preferably format it unambiguously. [For your
02/03/2004, make it 02 Mar 2004 or Feb 03 2004, for example.]

For your existing data which has been misinterpreted, try Data/ Text to
columns/ delimited/ out to the final stage then specify Column data format
as Date, & specify DMY or MDY as appropriate.
--
David Biddulph

Hello,
Not sure if anyone can help, but we are experiencing a problem with dates
in
Excel when the documents are sent to Japan. For example, I pass them
dates
and the dates (02/03/04) are being read as 2002/03/04 instead of
02/03/2004.
Any thoughts on how this can be fixed?

 

[David Biddulph]

A variety of solutions:
Change your reporting tool to give data that can't be misinterpreted.
If you can't get inside your reporting tool, use an intermediate processing
stage to do the step above.
At the import stage, try to force Excel to treat the data as text, not as a
date, and then you can use a consistent process to convert text in your
format to dates that Excel will recognise.
Another thing that may well be affecting what Excel does at the import stage
is the Regional Settings, not in Excel but in Windows: Control Panel; if
that has its date formats set to yy/mm/dd or yy/dd/mm that might well give
the symptoms you are describing. Try changing those settings on the import
machine and see what happens.
--
David Biddulph

The problem we're having is when the end user dumps data out of a canned
reporting tool (a querying tool that comes with our HR Database - ADP)
into
Excel. The canned reporting tool provides dates in the mm/dd/yy format,
and
as soon as she opens it up in Excel, the dates are EITHER formatted to a
yyyy/mm/dd (or perhaps yyyy/dd/mm - not sure which) or left alone if it
can't
reformat them.

For example:

09/01/04 gets converted to: 2009/1/4
11/01/00 remains the same: 11/01/00

I think Excel TRIES to convert the date fields to its yyyy/mm/dd format
but,
if it can't (i.e.: there is no day 00, so 11/01/00 stays the same) it
leaves
the field alone. What I'm ending up with is a report that has half the
dates
remaining in the original format (likely a text format) and the other half
being converted to the WRONG date. I'm stuck.

--
Thank you! - Jennifer

It sounds as if the date wasn't actually being stored as a date, but as
text, and the text was in such a form as to be ambiguous. For the
future,
store it as a date, and preferably format it unambiguously. [For your
02/03/2004, make it 02 Mar 2004 or Feb 03 2004, for example.]

For your existing data which has been misinterpreted, try Data/ Text to
columns/ delimited/ out to the final stage then specify Column data
format
as Date, & specify DMY or MDY as appropriate.
--
David Biddulph

Hello,
Not sure if anyone can help, but we are experiencing a problem with
dates
in
Excel when the documents are sent to Japan. For example, I pass them
dates
and the dates (02/03/04) are being read as 2002/03/04 instead of
02/03/2004.
Any thoughts on how this can be fixed?