Microsoft Office Forums Date Format Issue

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-04-2018, 07:54 AM
Jay_P Jay_P is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 64bit
Novice
Date Format Issue
 
Join Date: Jun 2018
Posts: 8
Jay_P is on a distinguished road
Default Date Format Issue

Hi All

Having some issues with a mail merge date format.



My data is coming in as 3/06/18 (D/MM/YY) using format switch DATE \@ "d" \*ordinal DATE \@ "MMMM yyyy which then converts to us format after the switch.

is there away to state that the data is in UK format?

I am using Office 2010

Any help appreciated.

Thanks
Reply With Quote
  #2  
Old 06-04-2018, 02:33 PM
macropod's Avatar
macropod macropod is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,535
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

Dates in a mailmerge are always treated as US-format dates - you need to use the appropriate switches to output them in any other format.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 06-05-2018, 04:09 AM
Jay_P Jay_P is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 64bit
Novice
Date Format Issue
 
Join Date: Jun 2018
Posts: 8
Jay_P is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Dates in a mailmerge are always treated as US-format dates - you need to use the appropriate switches to output them in any other format.
Are you able to advise what the appropriate switch would be?
Reply With Quote
  #4  
Old 06-05-2018, 05:44 AM
macropod's Avatar
macropod macropod is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,535
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

That depends on how you want the date to appear. To get a merged date to appear like:
5th of June, 2018
for example, you might use a field coded as:
{MERGEFIELD DATE \@ "'{MERGEFIELD DATE \@ d \*Ordinal} of' MMMM, yyyy"}
Note the use of paired double quotes and single quotes.

To see how to do just about everything you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial - it's in a 'Sticky' thread at the top of the Word forum: https://www.msofficeforums.com/word/...-tutorial.html. Do read the document's introductory material.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 06-05-2018, 06:12 AM
Jay_P Jay_P is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 64bit
Novice
Date Format Issue
 
Join Date: Jun 2018
Posts: 8
Jay_P is on a distinguished road
Default

thanks for the above I am still facing issue where if the day is 12 or less the format switch is seeing this as a US Date format and changing to 6th March 2018 instead of 3rd June 2018.

Any ideas how to prevent this?
Reply With Quote
  #6  
Old 06-05-2018, 06:15 AM
macropod's Avatar
macropod macropod is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,535
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

That suggests your source dates are not properly formatted (probably because there is a mismatch between your regional date settings and how the dates have been input).
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 08-17-2018, 01:23 AM
Jay_P Jay_P is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 64bit
Novice
Date Format Issue
 
Join Date: Jun 2018
Posts: 8
Jay_P is on a distinguished road
Default

Still having issues with Date switches changing to US Format, I have had all regional settings checked against the data source system , the host server and local machine all set to UK.

Each time I use a date switch this converts my date to a us format , I have tried using the following switches but each one keeps converting to a US Date format.
MERGEFIELD DOB\@ "d"\*ordinal MERGEFIELD EBZ101\@ "MMMM yyyy"
MERGEFIELD DOB\@ "d"\*ordinal MERGEFIELD EBZ101\@ "MMMM yyyy
MERGEFIELD DOB \@ "d MMM yyyy"
MERGEFIELD EBZ101\@ "d MMM yyyy"

When I leave the field with out any switches the data is kept in UK Format of DD/MM/YY

Any suggestions would be gratefully received
Reply With Quote
  #8  
Old 08-17-2018, 05:18 AM
macropod's Avatar
macropod macropod is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,535
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 is your data source?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 08-17-2018, 05:29 AM
Jay_P Jay_P is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 64bit
Novice
Date Format Issue
 
Join Date: Jun 2018
Posts: 8
Jay_P is on a distinguished road
Default

Data Source is a AS400 Based system.

All regional settings have been confirmed as set to UK.
Reply With Quote
  #10  
Old 08-17-2018, 07:47 PM
macropod's Avatar
macropod macropod is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,535
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

So how have you determined that the dates are in UK format without switches, but revert to US format with switches? Your use of what I assume is:
{MERGEFIELD DOB\@ "d"\*ordinal} {MERGEFIELD EBZ101\@ "MMMM yyyy"}
Seems odd, too, since you're getting the day from one field and the month & year from another.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 08-24-2018, 05:32 AM
Jay_P Jay_P is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 64bit
Novice
Date Format Issue
 
Join Date: Jun 2018
Posts: 8
Jay_P is on a distinguished road
Default

This issue seems to be happening no matter what date field I use from our system. when using the switch with the field below this is being populated in US format
MERGEFIELD EBTOZ2 \@"d"\*ordinal MERGEFIELD EBTOZ2 \@"MMMM yyyy

I am also having issues with a document that has a delay code in that is being converted to us format
Reply With Quote
  #12  
Old 08-24-2018, 04:38 PM
macropod's Avatar
macropod macropod is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,535
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

I cannot see how, unless your AS400 dates are being stored as strings instead of as date values, it would be possible to get the results you're describing. As for your reference to "a document that has a delay code", I'm not sure what you mean by that. Is it a reference to a date calculation as per my my Microsoft Word Date Calculation Tutorial: https://www.msofficeforums.com/word/...-tutorial.html?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #13  
Old 09-06-2018, 07:23 AM
Jay_P Jay_P is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 64bit
Novice
Date Format Issue
 
Join Date: Jun 2018
Posts: 8
Jay_P is on a distinguished road
Default

I have attached a copy of the template that I am having issues with show multiple date formatting switches.

I have also attached the data file from our host system, when I perform a manual mail merge the date field switches work correctly in all formats but when this is run automated through our system the date switches are showing in US Format.

I have had all regional settings checked and all are defaulting to English UK.

Any help with why word is switch to US format would be gratefully received.
Attached Files
File Type: docx Insurance Summary.docx (43.9 KB, 1 views)
File Type: txt Insurance Summary.txt (18.8 KB, 2 views)
File Type: doc Insurance Summary - Plain Paper-145509-06092018.DOC (44.8 KB, 0 views)
Reply With Quote
  #14  
Old 09-06-2018, 03:10 PM
macropod's Avatar
macropod macropod is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,535
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

Having examined your Insurance Summary.txt file, it is clear that the dates there have been stored there as text strings in a variety of formats, but all with the day preceding the month. Some even lack the year, which is in an adjoining field. And some are seriously corrupted (fields NIN2Z2, DBT2Z2, & NIN3Z2). Because the dates have been stored that way, they violate the OLE DB requirement for dates to be stored in the US-date format. Hence, when the mailmerge attempts to interpret the ones expressed in D/M/YYYY format as US dates in the M/D/YYYY format, you're getting bogus results. The only dates that would be rendered correctly in this scenario are those that are spelled out in full (e.g. field YYYCZ1, as 6th September 2018) and those where the first figure in the stored date is more than 12 (since months 13-31 don't exist).

IMHO, whoever coded your AS400 that way ought to be replaced. Dates should always be stored as such so the data are portable; any regional or other formatting should be handled as a display/print format issue.

That said, it should be possible to work around your present situation with field coding such as:
{QUOTE{SET IssDate "{IF{MERGEFIELD EFFALP \@ "d"}< 13 "{MERGEFIELD EFFALP \@ "M"}/{MERGEFIELD EFFALP \@ "d"}/" "{MERGEFIELD EFFALP \@ "d"}/{MERGEFIELD EFFALP \@ "M"}/"}{MERGEFIELD EFFAYR}"}{REF IssDate \@ "d" \* ordinal}{REF IssDate \@ "MMMM YYYY"}}
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #15  
Old 09-07-2018, 05:33 AM
Jay_P Jay_P is offline Date Format Issue Windows 7 64bit Date Format Issue Office 2010 64bit
Novice
Date Format Issue
 
Join Date: Jun 2018
Posts: 8
Jay_P is on a distinguished road
Default

Thanks for this, I have tried to rebuild this statement and receive either field error or a blank field once the merge completes.

Attached is a copy of how I have built the statement, would you be able to send through the statement within a word doc ?
Attached Files
File Type: docx QUOTE.docx (13.8 KB, 1 views)
Reply With Quote
Reply

Tags
date format, word 2010, word merge

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Format Issue Format Issue - shading shakology Word 2 05-21-2015 12:57 AM
Letter date changes when merging with Excel - not the format, the actual date! Smallweed Mail Merge 1 02-07-2014 06:00 PM
PDF Convert Format Issue budbon Word 2 12-19-2013 01:59 PM
Date Format Issue Field Format issue zjordan Word 7 04-18-2013 06:55 AM
Issue with date format in mail merge document walshjod Mail Merge 4 11-28-2012 04:46 AM


All times are GMT -7. The time now is 08:38 AM.


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