Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-07-2014, 08:51 AM
Smallweed Smallweed is offline Letter date changes when merging with Excel - not the format, the actual date! Windows 7 64bit Letter date changes when merging with Excel - not the format, the actual date! Office 2010 32bit
Novice
Letter date changes when merging with Excel - not the format, the actual date!
 
Join Date: Aug 2013
Posts: 13
Smallweed is on a distinguished road
Default Letter date changes when merging with Excel - not the format, the actual date!


This isn't the problem I keep reading about with UK to US date format but it must be linked. In my case the actual date of a Word letter (nothing to do with any dates in the Excel data source) is changing from 7 February 2014 to 2 July 2014 when I link to the data source.

The date is driven by a Word field:
{ DOCVARIABLE LetterDate \@ "d MMMM yyyy" \* CHARFORMAT }
We use this in our standard template and the field reads from the document variable LetterDate that is input via a dialog box. I've had a look (using VBA) directly at this variable before and after doing the link via mailmerge and it doesn't change from the initial value "7 2 2014". What must be happening is that the act of linking to the Excel data source is affecting the way the variable is being interpreted by the field: before the merge it interprets it UK-style and afterwards moves to US-style. We have just moved from Word 2003 to 2010 and this was not a problem in 2003.

I found the following link:
http://support.microsoft.com/kb/320473
and method 1 works - although this is obviously a different problem as there are no dates in the Excel data source or any other formatting there that is going wrong, just the existing date field already in Word.

I will now need to transmit this to the many users of this template. Can anyone please suggest an easier fix? These are people who are not used to navigating Word 2010's labyrinthine File/Options...
Reply With Quote
  #2  
Old 02-07-2014, 06:00 PM
macropod's Avatar
macropod macropod is offline Letter date changes when merging with Excel - not the format, the actual date! Windows 7 32bit Letter date changes when merging with Excel - not the format, the actual date! Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,040
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

As a workaround, you could change your LetterDate variable's format to numeric and store the dates in ISO format (ie YYYYMMDD). You could then use field coding to ensure the stored number is output in whatever date format you require, using a field coded as:
{QUOTE"{=MOD(MOD(DOCVARIABLE LetterDate,10000),100)}-{=INT(MOD(DOCVARIABLE LetterDate,10000)/100)}-{=INT(DOCVARIABLE LetterDate/10000)}" \@ "DDDD, DD MMMM YYYY"}
With this approach, you can change the date switch around to whatever day, month & year order and format you prefer.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date format in excel vba LaercioNB Excel 3 08-09-2013 06:33 AM
Letter date changes when merging with Excel - not the format, the actual date! excel 2010 to format a date jassie Excel 1 03-28-2013 02:33 AM
updating status date and actual duration ketanco Project 5 01-27-2012 07:32 AM
Letter date changes when merging with Excel - not the format, the actual date! Mail merge will not format date field generated by Excel IF statement borntorun75 Mail Merge 3 12-16-2011 06:28 AM
MS Project able to import actual start and or actual finish date from Excel? mhacker Project 0 04-26-2010 11:29 AM


All times are GMT -7. The time now is 12:08 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft