I have a report in an accounting program called MYOB. I chose to export the
report to Excel 2003. However, Excel shows the dates (found under one column)
in two different formats. i.e. some of the dates appear in this format
(04-April-2007), while others appear in another format which is (04/05/2006).

Also, some of the dates are flipped. For example the date originally
11-April-2007 becomes 04-Nov-2007

All these dates are under one column.

Could any one help me please?

I want these dates to be in one format (dd/mm/yy). But when I go to "Format
Cell" to pick my format, Excel does not change the formats of all the dates.
It only changes the dates that have this format (06-May-05)

I also want the dates to be correct and not flipped

This problem happens sometimes when the original dates in the MYOB report is
in US format (mm/dd/yy)

Please help me

Thanks

What application is the data being exported from? The solution may well be
there? I have had this problem when importing into XL from Business Objects,
and found the best way to solve it was in BO.

JB

"r" wrote:

I have a report in an accounting program called MYOB. I chose to export the
report to Excel 2003. However, Excel shows the dates (found under one column)
in two different formats. i.e. some of the dates appear in this format
(04-April-2007), while others appear in another format which is (04/05/2006).

Also, some of the dates are flipped. For example the date originally
11-April-2007 becomes 04-Nov-2007

All these dates are under one column.

Could any one help me please?

I want these dates to be in one format (dd/mm/yy). But when I go to "Format
Cell" to pick my format, Excel does not change the formats of all the dates.
It only changes the dates that have this format (06-May-05)

I also want the dates to be correct and not flipped

This problem happens sometimes when the original dates in the MYOB report is
in US format (mm/dd/yy)

Please help me

Thanks

On Apr 24, 4:18 pm, r wrote:
I have a report in an accounting program called MYOB. I chose to export the
report to Excel 2003. However, Excel shows the dates (found under one column)
in two different formats. i.e. some of the dates appear in this format
(04-April-2007), while others appear in another format which is (04/05/2006).

Also, some of the dates are flipped. For example the date originally
11-April-2007 becomes 04-Nov-2007

All these dates are under one column.

Could any one help me please?

I want these dates to be in one format (dd/mm/yy). But when I go to "Format
Cell" to pick my format, Excel does not change the formats of all the dates.
It only changes the dates that have this format (06-May-05)

I also want the dates to be correct and not flipped

This problem happens sometimes when the original dates in the MYOB report is
in US format (mm/dd/yy)

Please help me

Thanks

If Format|Cells is not working then your data is text. Just pray that
all inverted dates do have a month name and not a month number in
them. In a separate column enter (and copy down):

=--CLEAN(A2)

This should turn the dates into numbers. You can then apply formatting
to this column or copy/Paste Special...|Values to the original column
and then format to suit.

HTH
Kostis Vezerides

Thank you

"vezerid" wrote:

On Apr 24, 4:18 pm, r wrote:
I have a report in an accounting program called MYOB. I chose to export the
report to Excel 2003. However, Excel shows the dates (found under one column)
in two different formats. i.e. some of the dates appear in this format
(04-April-2007), while others appear in another format which is (04/05/2006).

Also, some of the dates are flipped. For example the date originally
11-April-2007 becomes 04-Nov-2007

All these dates are under one column.

Could any one help me please?

I want these dates to be in one format (dd/mm/yy). But when I go to "Format
Cell" to pick my format, Excel does not change the formats of all the dates.
It only changes the dates that have this format (06-May-05)

I also want the dates to be correct and not flipped

This problem happens sometimes when the original dates in the MYOB report is
in US format (mm/dd/yy)

Please help me

Thanks

If Format|Cells is not working then your data is text. Just pray that
all inverted dates do have a month name and not a month number in
them. In a separate column enter (and copy down):

=--CLEAN(A2)

This should turn the dates into numbers. You can then apply formatting
to this column or copy/Paste Special...|Values to the original column
and then format to suit.

HTH
Kostis Vezerides

Hi, I just encountered the same problem with sending a report from MYOB to
Excel 2003 where dd and mm is mixed up. I have checked with MYOB and they
told me that Excel 2003/2007 only recognise American date format mm/dd/yy and
not dd/mm/yy. Any date numeral 12 and below is interpreted by Excel to be
month and any numeral 13 to 31 interpreted as day. I was told by MYOB support
to switch the windows control panel date format to mm/dd/yy, otherwise the
way to get around is to send the MYOB report to HTML format and open the
latter using Excel.

This problem did not exist when I was using Office/Excel 2000. What I want
to know is why Microsoft has not done any all these years to resolve this
problem? Even the latest Office/Excel 2007 has the same problem. I would
appreciate if someone from Microsoft could address this question. Regards
Daniel

"randa" wrote:

Thank you

"vezerid" wrote:

On Apr 24, 4:18 pm, r wrote:
I have a report in an accounting program called MYOB. I chose to export the
report to Excel 2003. However, Excel shows the dates (found under one column)
in two different formats. i.e. some of the dates appear in this format
(04-April-2007), while others appear in another format which is (04/05/2006).

Also, some of the dates are flipped. For example the date originally
11-April-2007 becomes 04-Nov-2007

All these dates are under one column.

Could any one help me please?

I want these dates to be in one format (dd/mm/yy). But when I go to "Format
Cell" to pick my format, Excel does not change the formats of all the dates.
It only changes the dates that have this format (06-May-05)

I also want the dates to be correct and not flipped

This problem happens sometimes when the original dates in the MYOB report is
in US format (mm/dd/yy)

Please help me

Thanks

If Format|Cells is not working then your data is text. Just pray that
all inverted dates do have a month name and not a month number in
them. In a separate column enter (and copy down):

=--CLEAN(A2)

This should turn the dates into numbers. You can then apply formatting
to this column or copy/Paste Special...|Values to the original column
and then format to suit.

HTH
Kostis Vezerides