Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Showing todays date when the Excel value is zero
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 todays 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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Showing today's date when the Excel value is zero
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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Showing today's date when the Excel value is zero
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 todays 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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Showing today's date when the Excel value is zero
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 |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Showing today's date when the Excel value is zero
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 |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Showing today's date when the Excel value is zero
Use an If...Then...Else field that checks for "12:00:00 AM" being returned
-- 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 "David Hall" wrote in message ... 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 |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Showing today's date when the Excel value is zero
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 |
#8
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 |