File Merge Issues

[RTI451]

Hello, I recently created a merge document to generate IRS W8-BEN forms. I have two date fields that are formatted in Excel as mm-dd-yyyy. When I conduct the merge, however, the day of month comes in as "00" — e.g. a date shown as 04-01-2022 merges as 00-01-2022. I am attaching the merge file and a sample output here. Am I missing something? Thanks.

W8-BEN Factory.afpub 2021 W8BEN Merge Master.xlsx W8-BEN Merged.pdf

[thomaso]

Hi @RTI451,
Welcome to the Affinity Forums!

Could it be an issue with the "date" format in .xlsx? Is it possible to convert it to "text" before generating the merge? … Compare:

 

[RTI451]

Hi Thomaso, I do believe it may have something to do with formatting in Excel. I did successfuly try the Text method, but I am hoping to find a fix so that I can use a date formatted entry instead.

[Komatös]

It's the cell format. Maybe Publisher does not know the locale. I have set the date formats in the Excel file to English (UK). With this setting, the date is displayed completely, but in a different order.

[thomaso]

5 minutes ago, RTI451 said:

I did successfuly try the Text method, but I am hoping to find a fix so that I can use a date formatted entry instead.

In case the conversion for an entire table would be your concern: there seem to be various automated workflows:

https://www.wallstreetmojo.com/date-to-text-in-excel/

[RTI451]

Hi Komatös, thanks for the reply. I will take a look at the locale settings in Excel.

Edited May 2, 2022 by RTI451

[RTI451]

Hi Thomaso, thanks for the link, I will do some experimenting.