FWIW I also believe that this could be done better, but logically speaking,
more information would have to be transferred within Office to achieve that
in a way that would leave designers with no problems arising from
ambiguities. And even then, there are arguably still choices to be made,
somehow.
Let's simplify the design problem for a moment by assuming that it is
obvious that, by default, a date column in a merge data source should be
formatted according to the Regional Settings in Control Panel. (Although of
course /some people/ want their dates to be formatted the way they formatted
them in Excel, which may not be the same). There's already a design issue.
Which of the date formats in regional settings should Word use by default?
But what if Word does not or cannot know that the column is a date column?
For example, unless Word gets its Excel data from the obsolete Converter
(which arguably is a component of Word), it probably does not receive any
information from Excel that tells Word that a field is a date, a number or
any other type of data. When you use a { MERGEFIELD } field to insert data,
Word is inserting the text that it receives. Arguably, that is the right
thing to do: even though Word is clearly capable of parsing various date
formats correctly and therefore "guessing" that the column is probably a
date column, why should it assume that it is? What if the column normally
contains a piece of text that is not a date, but contains something that
looks like a date in some rows? Should it format the ones that look like
dates as dates? What if they are actually serial numbers of some kind?
AFAIK Word just displays the text as is. It's only when you try to apply a
formatting switch, or do a calculation, or perhaps do a comparison that Word
really has to try to determine what date the string contains etc. etc. In
other words, you have to tell Word that the text string is a date, and at
the moment the only way to do that is to use a date formatting switch.
In some cases Word may be theoretically capable of determining that a column
in a data source is a "date", but probably does not do so. I really do not
know. For example, when it connects to a database such as Access via OLE DB,
it could probably query the provider for the column type. That may be
feasible in Excel as well, except for an additional wrinkle, which is that
the cells in an Excel column do not necessarily all have the same format.
When Word gets data from Excel via ODBC or OLEDB, the driver/provider
determines a data type for the column (see
http://tips.pjmsn.me.uk/t0003.htm
if you are interested in the the gory details) then tries to coerce other
types of data into that format. If Word can actually discover the column
format from the driver/provider, then I believe it could format dates
correctly in the case where the driver has decided that the column is a date
column, because in that case the string transmitted always seems to be in US
Date format. However, I do not know whether Word can discover that
information. In a sense, you are probably better off ensuring that the
driver/provider decides that the column is a text column, because then the
driver/provider seems to use the format information in the Excel sheet. But
even that does not solve the problem completely - it's OK if you know the
sheet's dates are in the regional format you want. But what if the sheet was
prepared in a country where the format is different? Unless Word knows what
the original format is, it cannot reliably determine whether (for example)
10/12/2008 is 12th October or 10th December.
Incidentally, Word probably never sees a date in Excel's daynumber.timestamp
format, except when ODBC/OLE DB finds a date in a column that it thinks is
numeric, in which case Word will just see a piece of text which happens to
be a number. Then you also have to consider that some people may have
entered dates as text format (i.e. Excel hasn't even stored them as dates).
If you go slightly beyond that and consider that Word also lets you specify
the language of a piece of text, as a designer you then have to consider
whether a date (or number) should be formatted according to the standard
regional settings for that language (and since a language does not
necessarily imply a particular format standard (e.g. French speakers in
Canada may format dates differently from French speakers in France etc.,
there would even be problems attempting that).
Personally I think that the only way to provide the user with better choices
in this area is to start shipping data within Office (and perhaps elsewhere)
using ISO date/time standards (shame they didn't do that in the ODBC/OLE DB
driver if those formats were defined and in widespread use at the time,
which I think they probably were) in an XML format wrapper that allows
"original format" information to be transmitted. In that case, people who
also want other formatting info. from Excel such as bolding, italics etc.
could also be satisifed. I also think it would be very helpful if it was
easier in Excel for designers to specify a data type for a column and
prevent users from deviating from that. Perhaps that is already possible.
But people would still have to make choices in one way or another.
On the subject of Microsoft's multi-lingual, multi-cultural support, it is
probably quite interesting to compare what they have achieved with others. I
haven't done it for a while, but around 4-5 years ago I loaded up a copy of
Linux and one or two open source Office suites (probably Star- or OpenOffice
and KOffice) on the grounds that they had been trumpeting their
"internationality" for some time. But at that time the language support was
very disappointing (no LTR,RTL text support for example AFAICR), nor was
anyone able to suggest any other budget software that would do better. I
expect things have improved on that front - it's almost inevitably the case
given the way open source software is developed, but getting some of these
international issues "right" is not a trivial exercise for anyone,
especially if they also have a very large body of existing users to try to
satisfy.
--
Peter Jamieson
http://tips.pjmsn.me.uk
Tom said:
I would have thought that Word reads the underlying data as a number (Excel
stores dates as numbers) which it then displays as a date using the
American
date format. This algorithm could be modified to consult the system date
format (as set in Control Panel) and display the number as a date in that
format. A few lines of code would do it if they were minded to.
Graham Mayor said:
They are not going to change it.
Word reads the data in its raw underlying format, which is stored in an
American date format - hardly surprising given that it is an American
application. However all the tools required to format to match any
language
requirements are provided.
Up to and including Word 2000, Word connected to its merge data source
using
DDE (which is still available). DDE is unreliable, but it did bring over
the
formatting from the data source. From Word 2002 Microsoft changed to the
more reliable OLE DB method of connection. This does not bring over the
formatting, so if you want something other than the raw data you will
have
to format it in Word - which Macropod has demonstrated.
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
OK. But surely the question is why Word does this and how can MS be
persuaded to change it?
:
Word 2003 was no different.
--
Cheers
macropod
[MVP - Microsoft Word]
macropad - Thanks for the solution but it only goes to show how
crap MS software is and how arrogant Americans are. This happens on
my machine even though my regional settings are set to English
(Ireland). Why then should Word default to the US date format,
without giving me the option of changing this option?
This used not happen if Word previous to 2003.
:
Hi Ames,
You can solve this by adding a date formatting switch to your
mergefield. To do this: .. select the mergefield and press
Shift-F9. You should see something like: {MERGEFIELD myDate} ..
edit the field to include the formatting switch of your choice.
For example: {MERGEFIELD myDate \@ "d/M/yyyy"} or {MERGEFIELD
myDate \@ "ddd, d MMMM yyyy"}. The formatting options are the same
as you'd get in Excel. .. press F9 to update the field .. run your
mailmerge.
--
Cheers
macropod
[MVP - Microsoft Word]
Hi there..
I am mail merging date fields from excel spreadsheet listed in
format 24 September 2008 but when they come across into the word
doc they display as the US date format 9/24/08.
I have checked the language settings to make sure they are
English - Australian (not US) on the word doc. I have tried
selecting the correct date format when editing the toggle fields.
I'm stuck! help!! ...any suggestions??
