Date from Data Merge varies CSV vs XLS

[GatoL0c0]

I've have this weird bug when doing a data merge in V2.2.

If I try importing data from an XLS with a date, instead of displaying for example, 10/13/2023, it displays one day behind 10/12/2023. When I import the same file saved as a CSV, the data imports fine.

I'm on PC Windows 10 22H2 with the full Affinity Suite 2.2. I have experienced this bug in all prior versions of Affinity. I'll continue to used CSV instead of XLS but it's strange that the data won't merge correctly unless I use a CSV.

[Gnobelix]

22 hours ago, GatoL0c0 said:

I've have this weird bug when doing a data merge in V2.2.

If I try importing data from an XLS with a date, instead of displaying for example, 10/13/2023, it displays one day behind 10/12/2023. When I import the same file saved as a CSV, the data imports fine.

I'm on PC Windows 10 22H2 with the full Affinity Suite 2.2. I have experienced this bug in all prior versions of Affinity. I'll continue to used CSV instead of XLS but it's strange that the data won't merge correctly unless I use a CSV.

 

Hello @GatoL0c0,

Welcome to this forum.

I had a similar problem, I formatted the date in Excel user defined.

DD.MM.YYYY  (in German TT.MM.JJJJ)

I hope it helps.

 

Cheers

 

[NathanC]

Welcome to the forums @GatoL0c0,

It does look like a potential issue with the cell formatting if it works in the .CSV file, could you possibly send over the .XLSX file along with your data merge template file (.afpub) so I can confirm if the problem can be replicated? I've included a private upload link below if you do not wish to share the files publicly.

https://www.dropbox.com/request/OSEzQLhM2R8tML7dSbop

Thanks

[GatoL0c0]

On 9/24/2023 at 3:40 AM, NathanC said:

Welcome to the forums @GatoL0c0,

It does look like a potential issue with the cell formatting if it works in the .CSV file, could you possibly send over the .XLSX file along with your data merge template file (.afpub) so I can confirm if the problem can be replicated? I've included a private upload link below if you do not wish to share the files publicly.

https://www.dropbox.com/request/OSEzQLhM2R8tML7dSbop

Thanks

Here are the sample files. I have redacted some information due to privacy reasons.

Sample Excel_ 2023-24.csv Sample Excel_ 2023-24.xlsx Sample.afpub

[NathanC]

Hi @GatoL0c0,

Thanks for sending over the documents. As soon as I opened the .afpub I could immediately see that the date field in the document showed as 10/12/23, as soon as I delete the XLSX data source that's currently linked to the document and then re-add the Sample XLSX that you have attached, turn on 'preview with record' this then the date shows the correct 10/13/23 date. If I save the document, restart and re-open this correct date is also retained.

If you were to refresh the attached data source just as i've done above, does this not fix the date problem for you?

[GatoL0c0]

On 9/27/2023 at 5:50 AM, NathanC said:

Hi @GatoL0c0,

Thanks for sending over the documents. As soon as I opened the .afpub I could immediately see that the date field in the document showed as 10/12/23, as soon as I delete the XLSX data source that's currently linked to the document and then re-add the Sample XLSX that you have attached, turn on 'preview with record' this then the date shows the correct 10/13/23 date. If I save the document, restart and re-open this correct date is also retained.

If you were to refresh the attached data source just as i've done above, does this not fix the date problem for you?

Unfortunately deleting and relinking the sample or the original XLSX did not help. I also tried refreshing but still only the CSV file will show the correct date.

[GatoL0c0]

6 minutes ago, GatoL0c0 said:

Unfortunately deleting and relinking the sample or the original XLSX did not help. I also tried refreshing but still only the CSV file will show the correct date.

Discovered another issue with the XLSX. It does not display the time correctly unless I use a CSV.