Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail Merge in Word 2007 - trouble keeping time formatting
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! |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail Merge in Word 2007 - trouble keeping time formatting
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 buttonWord OptionsAdvanced) 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 "karen in NC" karen in wrote in message ... 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! |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail Merge in Word 2007 - trouble keeping time formatting
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?showto...#entry249 902 that may well include properly tested versions of the above. Peter Jamieson http://tips.pjmsn.me.uk karen in NC wrote: 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! |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word 2007 mail merge with Excel for more users at same time | Mailmerge | |||
trouble starting word 2007 each time | Microsoft Word Help | |||
Trouble keeping records straight for a mail merge -data from Acces | Mailmerge | |||
Word mail merge from Access - keeping the zeros in numbers | Mailmerge | |||
time formatting and mail merge | Mailmerge |