On Tue, 30 Oct 2007 17:39:02 -0700, Jim G
wrote:

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.

My guess is that Excel may be seeing the "dates that look like dates" as text,
and the serial numbers are the "real dates".


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.

No, you would just change your range object to refer to the data columns.
Since I did not know the layout of your data, I just used an example.

Here are other valid examples,

Set rData = Range("A1:D1000")

or

Set rData = Range("A1:A1000,C20:C25000,F1:F100")



--ron