Ron, thanks for the well considered response and suggestions.

Firstly, the regional dates are set in Windows control panel as DMY.

The accounting software is Axapta V3 which has a considerable population of
'bugs'. For example, I can send a report directly to Excel with ledger
transaction dates displaying as d/m/yyyy normally for several hundred rows.
Then the format will be lost and Excel will display the remaining several
hundred rows as their serial number. I modified the Job Project report to
force the format dd.mm.yy to avoid any ambiguity for my formulas/macros when
creating summaries from the report when it's sent to Excel. As you can see
it's opened a new can or worms. It drives me nuts. I'm hoping an upgrade to
V4 will fix some of these, but that's a way off yet.

Secondly, I'd like to give your suggestion a go. I can reformat the system
text dates to dd.mm.yyyy to save using the +2000 (although we have no data in
the 20th Century). Does "Set rData = [A1:A100]" mean I would need a
different macro for each column of dates (if I had more than one). Note
that, the number of rows can vary from a few hundred to thousands.

I'm still curious as to why the first run on the existing code works on the
first application and toggles back and forth for subsequent applications when
repeated manual conversions does not. However, I'm assuming I can add your
code to the existing code (after removing data type 4) and all will be good.

Cheers


--
Jim


"Ron Rosenfeld" wrote:

On Mon, 29 Oct 2007 22:01:00 -0700, Jim G
wrote:

Hi Ron,

My regional settings are set to d/m/y format.

I deduced from OssieMac's reply that the dates in dd.mm.yy format are text
generated by the accounting system as you suggest. However, a formula that
uses the dates needs the macro to convert 12.09.07 to 12/09/07 otherwise it
remains as text and the formula returns 00/01/1900.

When the accounting system generated the dates as per the regional default,
for some inexplicable reason rows 490 to 495 (for example) out of 1800 rows
changed to US date format. Thus began my quest for a workaround.

--
Jim

Jim,

OK, two points.

Just to make sure we're talking about the same thing, when I write "regional
settings", I am talking about the settings one sees going through Control
Panel, and not settings within Excel. I still suspect some discrepancy in
formatting, with Excel seeing certain output from Accounting as Text, and other
output as true dates.

Second, instead of using the Text-to-columns wizard to do the conversion, why
not try doing the conversion differently and directly.

Assuming ALL of the data comes out of the accounting package as dd.mm.yy,
perhaps something like this would work better:

================================
Option Explicit
Sub DateConvert()
Dim rData As Range
Dim c As Range

'set rData to include all the cells with date info
'or you could test that certain patterns exist
Set rData = [A1:A100]
For Each c In rData
c.NumberFormat = "dd.mm.yy"
If c.Text Like "##.##.##" Then
c.Value = DateSerial(Right(c.Text, 2) + 2000, _
Mid(c.Text, 4, 2), Left(c.Text, 2))
End If
Next c
End Sub
==============================

The c.NumberFormat line is to take care of an issue where the date might have
been coerced by Excel into something else.
--ron