OT: Mailmerge oddity Excel -> Word, Win-11

Dunno, but a possible workaround may be to have three date fields instead of one. You can use formulae in Excel to create 3 columns - one for day, one for month and one for year. You can then use the mail-merge to control how those 3 fields appear in the Word document. Or, if you only want one field, create a text string in Excel with the date in your chosen format, and use that in the mail merge.

Yup can be a PITA, sometimes you seem to have all the regional settings correct, but it still gives an unexpected format.

There is a way to explicitly choose the format of merge fields in Word - very handy for dates but also has lots of other applications like formatting numbers as currency etc.

So lets say the spreadsheet has a column with the title "Date". If you tell word to include this in the form letter using the "Insert Merge Field" button it will drop a token into the document that will look a bit like:

(the << and >> are single custom ligature characters)

If while viewing the document you do ALT+F9, that will change the view of all the merge fields to show you their actual content, and more importantly, make them editable.

So the <<Date>> will change to something in the form:

{ MERGEFIELD Date }

You can now edit that to include a format. You do this by adding a formatting marker "\@" after the name of the field, and then the actual format required.

So to render it as dd/mm/yyyy you would change it to read:

{ MERGEFIELD Date \@ dd/MM/yyyy }

(again the {} characters are not "normal" and you can't just type them - although you can enter a pair of them using CTRL + F9) [1]

The merge format characters vary in effect depending on how many you use and the case.

So if we were formatting 05/02/2023

dd/MM/yyyy -> 05/02/2023

d-MM-yy -> 5-02-2023

MMMM d, yyyy -> February 5, 2023

dd MMM yy -> 05 Feb 23

Or you can get posh with :

{ MERGEFIELD Date \@ d \* Ordinal }{ MERGEFIELD Date \@ MMMM }{ MERGEFIELD Date \@ yyyy }

Which gives -> 5th February 2023

Handy if you want to write your own merge fields with conditional logic. e.g.

{ IF { MERGEFIELD Title } = "Mr" "Dear Sir," "Dear Madam," }

I'm not the OP but that is incredibly useful, thank you, saved in my help directory :-)

Well, dash my wig!

Thanks John, I will transmit that to the chap having the issue. I tried this also with Office 2016 for macOS, and somewhat to my surprise switching the view of the field codes works with ALT-F9 there too. Or by right-mouse on the field code and select "Toggle field codes" (whether it does them all or just that one, I didn't test).

I take it you don't know many Doctors? :)

Owain

What was the "format" type of the date column, hopefully "date" rather than "text" or "number/general"?

On 22 Feb 2023 10:44:47 GMT

I had this problem years ago, but with Access and Excel. What was odd was that only a few of the dates came out wrong, the rest were right. The penny eventually dropped: the correct dates were where the day number was greater than twelve.

Here also, both Access and Excel had correct regional/cultural settings, but the transfer process between them didn't respect either. It treated the dates as US, *except* when the month number would be greater than 12, when it realised that would be wrong and used the UK format. What idiot programmer did that? If one is wrong, they're all wrong.

I can't remember now how I fixed it, but there are several possible workarounds. You just have to know that you need to use one of them. These days I tend to use ISO format (yyyy-mm-dd) for dates internally, with the added advantage that an alphabetic sort gets them in the right order.

You just need an illegible handwriting font for them! :-)

The IF THEN ELSE logic gets a bit more cludgy in layout if you need nested clauses:

{ IF { MERGEFIELD Title } = "Dr" "Dear Doctor," "{ IF { MERGFIELD Title } = "Mr" "Dear Sir," "Dear Madam," } }

You can carry on sticking extra IF constructions into the third parameter of the preceding construction to create a nested chain:

If then Something else if something else something else else if yet something yet somethiung endif endif endif

Alas now switch or case statement! (if it gets complex it is probably better to do some of the work in the data source first)

Some Macs need Fn ALT-F9, but yup the process should be the same on both.

One thing to check is that you have explicitly formatted the column in excel etc as a date field. Sometimes it will infer that it should treat a number as a date, but it not actually be explicitly set to that.

I was lucky enough to have an appointment with one once, about 3 years ago from memory!

I store all dates/times as seconds since the epoch, as this is the data storage layer. Those are then converted for presentation to the user, with the format chosen by the user.

I suspect that dates put in before the change to the correct format will always be wrong but those after will be correct. This is or was in a very old version, so I'm surprised if its still the case. Brian

What is an old version? Chummy was using Office 365 under Win-11.

I on the other hand use Office 2016 under macOS, still perfectly adequate for all that its Help insists that this version has been retired. But since the app continues to work it can't have been retired.

If it doesn't display consistently within excel, suggests not imported correctly, unlikely to merge properly until you fix it inside excel, did it start life as a .csv?

Mine is 2007!

Not according to support.microsoft.com, who say:

Support for Office 2016 for Mac ended on October 13, 2020. Rest assured that all your Office 2016 apps ...

I only got it because Office 2008 is only 32-bit and now I've moved to Catalina, 32-bit apps don't run.