Why does Mail Merge give an unexpected value?

[EatMoreBacon]

I'm frustrated.

In template4, I have two data merge Layout. One is working as expected giving me the info for each date cell.

The other is giving wrong values.

Instead of the month and year as shown on spreadsheet, it gives 0000 2024

Instead of the Day of Week as shown on the spreadsheet, it moves all my days by one.

I can find no reason for why it acts the way it does. I've double checked my formulas. I've tried pasting values (file attached). Any suggestions?

Birthday Calendar template4.afpub Birthday Calendar trial.afpub Book1_exp.xlsx

[NathanC]

Hey @EatMoreBacon,

On 11/22/2023 at 2:05 PM, EatMoreBacon said:

I can find no reason for why it acts the way it does. I've double checked my formulas

I don't think that there's anything wrong with your custom formulas in Publisher, rather the app is at fault here when interpreting the formulas for the merge fields. To test this I created a new .XLSX file using your Date/Year field, an Extra Month only field and the Weekday field all using custom formulas and some random dates put in. Both the Date/Year field and Month only field both failed to display, however my Weekday field worked as expected using your same formula, so this could be an intermittent/doc specific problem.

However, what did work was saving my sheet out as a standard comma delimited CSV file from Excel, and using that as the data source instead of the .XLSX file, all of my fields then displayed as expected in Publisher, so it may be worth giving this a go with your spreadsheet.

I'll be getting this date custom formula issue logged with the developers.

[EatMoreBacon]

I also found that doing a Lookup and returning actual text works. So I wonder if it has something to do with the custom formatting.

[lacerto]

Obsolete.

[Affinity Info Bot]

The issue "Certain custom date formulas from XLSX are not importing correctly into fields" (REF: AF-1284) has been fixed by the developers in internal build "2.4.0.2213".
This fix should soon be available as a customer beta and is planned for inclusion in the next customer release.
Customer beta builds are announced here and you can participate by following these instructions.
If you still experience this problem once you are using that build version (or later) please reply to this thread including @Serif Info Bot to notify us.