Mail Merge and Excel

[Carl_61]

Hello everyone, I could really use some insight here.  I am pulling out my hair as I cannot figure out what is going on.  I have rows of records from which I am trying to merge some of the info into Mail Merge. The normal data does not seem to be a problem. However, when I try to merge in Dates this is where the trouble begins. In my Mail Merge Letter I have Merge Fields that have From and To Dates that I am trying to merge in but I keep getting numbers that represent the dates.  Especially the To Date and some other dates I have in the letter.  Only the From Date is displaying correctly. So, as an example, I have: Monday, March 18, 2024 to 45375 when it should display as Sunday, March 24, 2024.  I have applied the switches and yet I still keep getting the numbers.  The date for the To Date in the Excel workbook is calculated if this makes any difference. =IF(M2="","",M2+6).  Can anyone help me figure this out?  I really need to perform these Mail Merges but these numbers being generated will mean nothing to no one. Seems like such a simple process yet being so complicated.

Here is the Merge field set up but regardless of this it is just showing the numbers on the merge/preview.  {MERGEFIELD End_Date \@ "dddd, MMMM d, yyyy" }.  What am I missing??

Thank you to any folks that can help me figure this out.

Carl

[#2]

@Carl_61 Does the first answer in this thread help? https://stackoverflow.com/questions/60357563/date-format-mail-merge-word-excel 

[#3]

I wish it did. For some reason my

[#4]

I wish it did. For some reason my End Date, which a calculated date in Excel, in its own cell, just keeps being returned as numbers. I even change the End Date, to see what would happen, to a hard date(not calculated, but I still get the numbers, not the date. I am super puzzled!

[#5]

Is there any row in the data sent to Word that has data that isn't a date, deviates from that date format, or perhaps is empty?

[#6]

Yes, some cells in the column are empty. This list is a room roster whereby some rooms are empty so no begin date or end dates are in those cells along the empty room rows.

[#7]

What makes this odd then is that the begin dates for all the occupied rooms are merging correctly. It’s just the end dates, calculated based on the begin date, that just keep showing up as numbers. I’ve tried changing the Format and everything I know.

[#8]

Can you share an anonimized copy of both the Excel file and the mail-merge doc?

[#9]

Yes, I will work on that

[#10]

@JKPieterse 

Attached are the 2 files that I am working with.  As you can see, most data has been removed and or altered.  I have tried everything I can think of but I keep getting numbers for the End Date.  This is driving me insane.  I have tried switches but nothing changes for me in regards to the end date.  HELP PLEASE

[#11]

Are there settings in settings that may not be correct? Who would ever want to see the date numbers as no one would understand what the date is anyway. For visualizing, this seems like such a waist.

[#12]

@JKPieterse 

Attached are Pics of what I am getting or how mine is set up.  No matter what I do I keep ending up with numbers for the end date.

Carl

[#13]

@JKPieterse 

I shared my files per your request and hoping you can tell about or help me with, this albatross of a mail merge.  I just don’t get it. If I have to call Microsoft directly would you know the best number to call?

[#14]

For me it works just fine provided I make sure the Excel file is not open in Excel when I open the Word file.