mergefield time format switch ignored

[Pauline Evans]

I am doing a merge from Excel 2003 to Word 2003, and the field is formatted
as time in Excel (just time, no date). In Word I am trying to use a format
switch to format the time since I read that Word just gets the raw data from
Excel. My field is written as { mergefield Time \@ "HH:mm" }, which for a
value in Excel of 0:05 should give me 00:05 but instead gives me 12/30/1899
12:05:00 AM. I have tried adding or taking away spaces, single vs. double
quotes or no quotes, varying time formats, and the result is always the same.
I have searched forums for an answer and have seen other people with the same
question but have not yet seen an answer beyond what I have already tried.

 

[Graham Mayor]

I have tried, but can't reproduce this. If you enter a time in Excel as
00:05 then Excel gives this a custom format of hh:mm. The underlying data in
Excel (see the command line) is 00:05:00. If you format the cells as TIME
then depending on which format you select the time could be shown as
00:05:00 AM.

You could get the results you report if the times in Excel are formatted as
dates, but I would have thought that would have been obvious.

If you are sure that your times are correctloy formatted then use the
alternative connection method to attach your data file - see the Excel data
section of http://www.gmayor.com/mail_merge_labels_with_word_xp.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Pauline Evans]

I had read about the possibility of using DDE, and will try it, but I was
concerned about possible unintended side effects since I am designing this
for use by other people in my group, and I would (I assume) have to make this
change to Word option settings on each PC where someone might need to use
this document. What other effects might this change have? I don't know of any
other mailmerge documents being used but I'm not familiar with every document
used within my group.

 

[Graham Mayor]

The only effect this has is to give a choice of conversion options. It will
have to be made on all machines as you suspect. I still think there must be
something amiss with your Excel data formatting as I cannot recreate the
problem.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Pauline Evans]

In Excel, when I select one of these cells and do Format Cells, it comes up
showing the cell formatted as Number, Category Time, Type 13:30. In the line
at the top that shows the contents of the cell, it says 12:05:00 AM, which it
says regardless of which time format I choose. (If I change it to Date
format, that does change, but I made a point of not using a Date format.)

 

[Graham Mayor]

When I configure times (the same times) in an Excel sheet and format it this
way - the data comes across correctly as
12:05:00 AM without a switch and formats according to whatever switch is
added

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Peter Jamieson]

which it

says regardless of which time format I choose

That isn't what happens here, and I wonder if there might be a problem in
your Excel sheet, especially if it has been upgraded from an earlier version
of Excel. (It probably isn't important, but are you using the English (U.S.)
locale ?)

Peter Jamieson

 

[Pauline Evans]

Yes, I am using English (U.S.). As for the version, I created the spreadsheet
from scratch using Excel 2003, and my PC was recently reimaged, so even if it
had previously had an older version of Excel all traces of that should have
been wiped by the reimaging process (they put on the same image as for all
new PC's). I've gone the DDE route, and the formats are now OK in my Word
form, so I guess I won't worry anymore about this, since it seems I'm
(apparently) doing everything right. (It's just annoying to have an unsolved
problem - there must be something wrong somewhere, and the next time there is
something odd, I'll wonder if it's related in some way.)