 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / March 2006mergefield time format switch ignored
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. 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  Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > 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 [quoted text clipped - 7 lines] > same question but have not yet seen an answer beyond what I have > already tried. 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. > 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 [quoted text clipped - 20 lines] > > same question but have not yet seen an answer beyond what I have > > already tried. 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. Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > 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 [quoted text clipped - 39 lines] >>> people with the same question but have not yet seen an answer >>> beyond what I have already tried. 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.) > 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 [quoted text clipped - 44 lines] > >>> people with the same question but have not yet seen an answer > >>> beyond what I have already tried. 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 :( Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > 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 [quoted text clipped - 59 lines] >>>>> and have seen other people with the same question but have not >>>>> yet seen an answer beyond what I have already tried. > 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 > In Excel, when I select one of these cells and do Format Cells, it comes > up [quoted text clipped - 55 lines] >> >>> people with the same question but have not yet seen an answer >> >>> beyond what I have already tried. 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.) > > which it > > says regardless of which time format I choose [quoted text clipped - 65 lines] > >> >>> people with the same question but have not yet seen an answer > >> >>> beyond what I have already tried. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.