Date Format Issue

[Jay_P]

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

[macropod]

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.

[Jay_P]

Originally Posted by macropod

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?

[macropod]

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.

[Jay_P]

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?

[macropod]

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).

[Jay_P]

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

[macropod]

What is your data source?

[Jay_P]

Data Source is a AS400 Based system.

All regional settings have been confirmed as set to UK.

[macropod]

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.

[Jay_P]

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

[macropod]

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?

[Jay_P]

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.

[macropod]

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"}}

[Jay_P]

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 ?