Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Showing today's date when the Excel value is zero
Graham
Fantastic, that works. Thank you David Hal "Graham Mayor" wrote: What you need is: {IF{MERGEFIELD E_Start} "12:00:00 AM" {MERGEFIELD E_Start \@ "d MMMM yyyy"}"} -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org David Hall wrote: Graham, thanks again for persevering with this. Removing the switch, saving the document and reopening (to make sure all changes are applied) produces a field entry in Word of: 12:00:00 AM When the Excel value is 0. When I remove the date formatting from the Excel cell then the Word field shows zero if the Excel value is zero. However, the result when the Excel cell contains a date the Word field contains the numerical value of the date, ie 36008 for 1st August 1998. This result is unaffected by inserting the switch! (Again I saved and reopened the documents) I hope this helps you diagnose what I have got wrong. David Hall "Graham Mayor" wrote: I appreciate that when the field has content it produces the date, but it is what the field produces when the data is 0 that matters. Word interprets the raw data and not necessarily what you see displayed in Excel, which is based on the formatting of the cell. So what *exactly* does the field (without a switch) produce when the cell content is 0? Today's date can be expressed in a variety of ways - how does the field express it? I have to say that correcting the Excel data is the better plan if that is available to you. Not everyone has that option -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org David Hall wrote: Graham thank you for your comments. Whenever a date is present in E_Start the correct date is inserted in the Word Doc, unfortunately I was unable to achieve a solution with your suggestion. The mailmerge is executed from a sheet that contains one row of data. Which information is in this row is determined by a selection process elsewhere in the workbook. Therefore if the main data has a blank cell the corresponding cell in the row for the merge data shows a zero. I tried with a test Excel Doc and test Word Doc and got the same 'error'. I find it really weird that a zero in the Excel cell is 'forcing' today's date to be entered in the Word Doc. Re-writing the Excel entry so that if the main data cell is equal to zero (ie blank) the corresponding merge cell is to equal blank ( "" ) does solve the problem. So perhaps this is the best solution. Obviously if another solution is possible I would be very interested to receiving further postings. David Hall "Graham Mayor" wrote: If today's date is being inserted into Word when there is a 0 in the data file, then the merge is never seeing 0 as the result of the merge. You have to check for what is *actually* being merged and not what you expect to be merged. What *exactly* does {Mergefield E_Start} produce? You can then test for this instead of 0. If indeed it is today's date (provided today's date is never E_start) then {IF{MERGEFIELD E_Start} {Date}"{MERGEFIELD E_Start \@ "d MMMM yyyy"}"} might be nearer the mark. You will probably have to add switches to both the first E_Start and Date. You may find it simpler to use an alternative means of connecting your merge to the Excel data - see the Excel data section of http://www.gmayor.com/mail_merge_lab...th_word_xp.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org David Hall wrote: Hi, this problem is really frustrating me so if you can help I would really appreciate it. I am using Word 2003 and Excel 2003 to do a mailmerge and I am having a problem with today's date being inserted in the Word document when the Excel value is zero. I would either like to show the date that is entered in the Excel sheet or nothing if the Excel value is zero. My entry in the Word document is: {IF{MERGEFIELD E_Start} = 0 "" {MERGEFIELD E_Start \@ "d MMMM yyyy"} Where am I going wrong? Many thanks David Hall |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The Web Archive Converter puts todays date on MHT copies of old f | Microsoft Word Help | |||
Mail Merge of a Date from Excel Data into Microsoft Word | Microsoft Word Help | |||
Excel date format merging to Word XP version | Mailmerge | |||
How to prevent Merged Date Field showing time? | Mailmerge | |||
Date issue merging from Excel field in Date format to Word | Mailmerge |