Mail Merge in Word 2007 - trouble keeping time formatting

[karen in NC]

My Excel database has a column of arrival times I have formatted as time i.e.
1:30 PM and when I try to merge this information into a Word document it
gives me a decimal i.e. 0.378843. It is obviously seeing it as a formula
with division, but I want it to come into Word as 1:30 PM. Please help -
only way I have found around it is to change the time info in Excel to 1.30
PM ...

I appreciate any help you can give me to solve this problem!

Thanks!

 

[Doug Robbins - Word MVP on news.microsoft.com]

How do you have the data formatted in Excel?

If I format the Excel cells with the 1:30 PM time format and use the default
method of connection of the data source to the Mail Merge Main document (
that is the "Confirm file format conversion on open" box is left unchecked
under Office button>Word Options>Advanced) then the merged data appears as

1:30:00 PM

If you do not want the seconds to appear, press Alt+F9 to toggle on the
display of the field codes in the mail merge main document and add the
following formatting switch inside the closing } of the mergefield

\@ " h:mm am/pm"

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Peter Jamieson]

Times are stored as fractions of a day (i.e. 0.5 is 12 noon) in Excel.
If you are seeing them as numbers then it's possible that you have some
data in the first 8 rows in that column that is actually formatted as
numeric - in that case the OLE DB provider that Word uses to get data
from Excel will see the whole column as numeric. (see
tips.pjmsn.me.uk/t0003.htm for some details on that, but it sounds as if
you may have encountered a situation that is not described there).

So you can try to ensure that all the first 8 values in the column are
perceived to be times.

Or, to get the time /as formatted in Excel/ you may be able to connect
to Excel using DDE - in Word 2007, click the Office button, Word
Options, Advanced, General and check "Confirm file format conversion on
open". Then go through the process of selecting your database again and
select the DDE option in the extra dialog box.

Finally, you can recalculate the time from the fraction using { = }
fields in Word, e.g. if your field is called mytime, then the steps are
something like

{ SET T { MERGEFIELD mytime }
}{ SET T1 { =24*{ REF T } }
}{ SET H { ={ int({ REF T1 }) }
}{ SET T2 { =60*({ REF T1 }-{ REF H }) }
}{ SET M { ={ int({ REF T2 }) }
}{ SET T3 { =60*({ REF T2 }-{ REF M }) }
}{ SET S { ={ int({ REF T3 }) }
}{ SET ltime { QUOTE "{ REF H }:{ REF M }:{ REF S }" } }

then you should be able to use the ltime bookmark with time formatting e.g.

{ REF ltime \@"hh:mm AM/PM" }

All the pairs of {} need to be the special field code braces that you
can insert using ctrl-F9. You can abbreviate the above by eliminating
intermediate steps, etc. etc. macropod has lots of useful stuff on
date/time field calculations at at:
http://www.wopr.com/index.php?showtopic=249902&st=0&p=249902&#entry249902
that may well include properly tested versions of the above.

Peter Jamieson

http://tips.pjmsn.me.uk