Hi Ron,
I tried this without any luck. My dates are YYYY so I amended the code to
accomodate this.

The worksheet runs the normal macro on each update so this must have
resulted on cell changes because it went into a loop. Interesting, but too
much for me.

It looks I'll have to make sure that the original macro is only run once
(99.9% of the time). Othewise, I can run thrice to force the toogle back to
dd/mm/yyyy.

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