Different regional date settings in reports

[Piri]

Excel 2000
We extract data from an Access database into a spreadsheet. This data
includes dates.

We operate under NZL regional systems settings (similar to UK) but the
report recipients want the spreadsheet sent to them in US formats.
That is apparently because the spreadsheet is automatically picked up
by their system for analysis, and it is is spitting out our dates. At
that end of the process there is apparently no way for the date format
to be changed before it is processed. ie manually changing the
spreadsheet at their end first.

We can produce the desired format by changing our own system settings
to US, do the job, send the spreadsheet, then revert the settings back
to NZL when we are done - but this is a regular report.

That is hardly satisfactory and there must be a way of delivering the
requested "format".
I would have thought the a date is a date (an underlying value) and
the formating is merely a view of that value.

Is there a way of presenting the date in our spreadsheet (say as a
text string) that would be read at the other end as a date
"formatted" [US] in the way they require?

Any ideas appreciated.

WSF
ps not really sure I am making a lot of sense here.

 

[Bob Phillips]

Piri,

As you say, dates are just numbers and the format is just a view, so there
should not be a problem.

However, there is an issue with VBA and dates, but it usually occurs for us
(non-US) and not the other way around. Are you using VBA at all in these
reports?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[NickHK]

If you have Excel 2002 or up, look into format the date cell in one of the
formats marked with an *.
From the Format Cells:
Except for items that have an asterisk (*), applied formats do not switch
date orders with the operating system.

NickHK

 

[cbhartness]

I have an application where I assigned different formats to variables
and apply the formats to the cells as needed. It is set up to convert
from various country formats to various OS formats. You need just a
subset of that, such as.

dim dateTimeFmt as String
dateTimeFmt = "dd/mm/yyyy hh.mm"
Range("A1:C1").NumberFormat = dateTimeFmt

Carl.