Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 06-26-2012, 04:30 AM
dan1 dan1 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Jun 2012
Posts: 5
dan1 is on a distinguished road
Default Date picker mail merge error.

I want excel to use a DTPicker to fill the cell with the selected date (linked cell) the for that cell to be used in a mail merge.



But I am having problems with days between 1 and 12.
I'm using a DTPicker so there is no room for user data error.
I have the dtpicker format set to short date - "dd/mm/yyyy" (Have tried long too)
I have the merge field set to long date - "dd mmmm yyyy"
I have the regional settings for my computer set to English (UK) - short date as "dd/mm/yyyy" and i have tried formatting the excel cell in every date format, text, custom etc

When I enter the date 12/01/2012 (meaning 12 Jan 2012) that is the way it is stored in excel (via linked cell). When you do the mail merge it shows as 1 December 2012, but if i put the merge field to DD/MM/YYYY format it reads correctly as 12/01/2012. What really weird is that if i select a day after 12th ie 13/01/2012 it puts it in the mail merge correctly as 13 January 2012!!! I could understand if it was stuck in US format but it seems as though if the date can be US (ie day under 12, recognised as month) it will put US (despite everything being set to UK format) but if not it will put UK format. It makes no sense.

So technically it is still day month year, but the day and month switch on their own i the day is before the 12th!. The other funny thing is that if you go back to a short format and the date still shows 12/01/2004 as it should.


Any ideas?
Reply With Quote
  #2  
Old 06-26-2012, 06:45 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,355
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

hi Dan,

There are two parts to this issue:
• the date format in Excel; and
• the date format in Word.

Word has a number of different methods of connecting to mail merge data sources, including DDE and OLE DB. Word 2002 and later use the OLE DB coonection by default, though you can change this (to DDE, for example). To work around a limitation in the OLE DB provider used to get data from Excel etc., when Word is connected to an OLE DB data source, it treats dates as if they are in the US mm/dd/yy format, regardless of the format in Excel, your regional settings etc. Applying a date format switch fixes that - and gives the mailmerge document the ability to format the date independently of whatever format is used in the data source.

To get the date format you want, you can add a formatting picture switch as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
• delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy";
Note: Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.
• position the cursor anywhere in this field and press F9 to update it.

I assume that in Excel you're using the Calendar Control. If you use that, whatever date you pick should be inserted into your destination cell in that cell's format. If the day & month order for that format differs from your regional settings, that may cause problems for your mailmerge.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 06-27-2012, 03:23 AM
dan1 dan1 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Jun 2012
Posts: 5
dan1 is on a distinguished road
Default

Thanks for your reply Paul but I am using {MERGEFIELD Appt_date \@ "dddd d MMMM YYYY"} in the merge field. I understand that the format needs to be defined on the mail merge as well but this doesn't solve the problem. It shows it in the correct format but recognises the month and day the other way aroun ONLY if the day is before the 12th of the month!

So the Microsoft date picker says 03/07/2012 in UK format (3rd July). The linked cell on excel then dispays 03/07/2012 in UK format (I have also tried longer formats on both (3 Jul 2012 etc). The the merge field is set to {MERGEFIELD Appt_date \@ "dddd d MMMM YYYY"} but comes out as Tuesday 7 March 2012. The same if I use \@"dd/MM/YY" (7/3/2012). But if i select 13 July everything is correct in Excel and Mailmerge.

Weird. You help is much appriciated.

Thanks

Dan
Reply With Quote
  #4  
Old 06-27-2012, 03:38 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,355
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

What happens in Excel when you format the linked cell as DD-MMM-YYYY?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 06-27-2012, 03:55 AM
dan1 dan1 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Jun 2012
Posts: 5
dan1 is on a distinguished road
Default

The same. Its shows as 03-Jul-12 in the cell but on the merge as 7 March 2012. Unless I put 13-Jul-12 then it becomes 13 July 2012.

It seems as though it is something to do with the merge field code as if I just add the field without amending the code it comes up as 03/07/2012 or 13/07/2012. But when i add code it seems to confuse it even if i put code in the same format as the default (\@ "DD/MM/YYYY").

If it was either US format or UK format all the time i would think it was just something i was missing but the fact that if after 12th it switches is weird. Surely if it is set to US as it appears to be that when you put 13/07/2012 it would create an error.
Reply With Quote
  #6  
Old 06-27-2012, 04:06 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,355
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You could try repairing the Office installation (via Word Options|Resources|Diagnose) to see if that makes any difference.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 06-27-2012, 04:38 AM
dan1 dan1 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Jun 2012
Posts: 5
dan1 is on a distinguished road
Default

No joy but thanks for your time.
Reply With Quote
  #8  
Old 06-28-2012, 03:00 AM
dan1 dan1 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Jun 2012
Posts: 5
dan1 is on a distinguished road
Default Solution

Hello all - I found this solution


http://www.excelforum.com/excel-gene...wrong-way.html


Thanks
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Picker Andy2011 Word VBA 4 11-24-2012 10:07 PM
word 2003 date picker nashville Word 16 04-06-2012 04:12 AM
Date picker trintukaz Excel 0 12-30-2011 12:42 AM
Calculations using values from date picker controls Inkarnate Word 0 06-09-2010 07:16 AM
Word 2007 date and time picker dmcohio Word 2 04-09-2010 04:13 AM


All times are GMT -7. The time now is 09:56 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft