Ensure dates imported into Excel 2003 are shown in DD/MM/YYY form.

[Guest]

Have recently upgraded to a new XP Pro PC with Office 2003

However when exportng accounting reports from MYOB Premier 8, I now find
that Excel treats the MYOB dd/mm/yyyy format dates (Australian format) as
mm/dd/yyyy (US format)

My Win XP Region setting is Australia and my Language setting is English
(Australian)

How can I adjust the Excel Options to ensure the defualt date format for new
Excel sheets is dd/mm/yyyy ? (there's nothing on this in the International
tab)

Kym Yeoward
Alice Springs NT
Australia

 

[Guest]

Kym

In your WinXP Regional Settings - try having a quick look into the settings
by clicking on Customize (Regional Options - Customize - Date - Short Date)
- although you are setup as English(Australia) somewhere along the way it's
possible somebody selectively changed the date format (Seen this happen here
with English{Ireland}).

Steve

 

[Ron Rosenfeld]

Have recently upgraded to a new XP Pro PC with Office 2003

However when exportng accounting reports from MYOB Premier 8, I now find
that Excel treats the MYOB dd/mm/yyyy format dates (Australian format) as
mm/dd/yyyy (US format)

My Win XP Region setting is Australia and my Language setting is English
(Australian)

How can I adjust the Excel Options to ensure the defualt date format for new
Excel sheets is dd/mm/yyyy ? (there's nothing on this in the International
tab)

Kym Yeoward
Alice Springs NT
Australia

Frequently, when this sort of problem is reported, and both the Control
Panel/Regional Settings as well as the Excel formats are correct, the actual
problem turns out to be the format of the exported data (from MYOB).

If MYOB is exporting data in the US format, then Excel will convert some of the
data to Australian format and leave some of the data as text.

If you could give some examples of the actual data; and also execute an
ISTEXT() function on the erroneous data, we could better help.




--ron

 

[ronniecoote]

If MYOB is exporting data in the US format, then Excel will convert
some of the

data to Australian format and leave some of the data as text.

If you could give some examples of the actual data; and also execute an
ISTEXT() function on the erroneous data, we could better help.

The problem appears to be with MYOB. You can 'fix' this (at least
generate proper dates in dd/mm/yyyy format) by applying the following
formula (assuming that the MYOB date you want to alter is in cell B4):

=IF(ISTEXT(B4),DATE(RIGHT(B4,4),MID(B4,4,2),LEFT(B4,2)),DATE(YEAR(B4),DAY(B4),MONTH(B4)))

This checks whether the cell is 'text' (i.e. Excel has been unable to
convert it to a date). If it is text, the formula constructs an
appropriate date by parsing the text string. If the date has been
interpreted as a date by Excel, the formula swaps the month and day to
give the proper date, as MYOB intended.

RC.