Error in date imported from XLSX

[sstlaurent]

Using a XLSX file with 3 columns of dates. Same date each line but format is different like this:

date courte    date longue    jour
2023-01-02    2 janvier 2023    lundi

When using data merge in Publisher, the first column with value 2023-01-02 changes the value to the date minus one day 2023-01-01. The other columns are ok. I've tried a few scenarios, always this error. What is happening?

Using Publisher 2.0.3 in French, Windows 10 in french.

calendrier 2.xlsx bogue de fusion.pdf test données 3 - XLSX et CSV.afpub

Edited January 4, 2023 by sstlaurent

[Old Bruce]

I think it has to do with the formatting of the dates, A2 to A29. When using Formatting like this for dates or numbers in general there is always a chance that the numbers will be misread by the importing  application. Export a CSV and use that, or copy paste results of formulas into the spreadsheet or a new one.

[Pyanepsion]

I too have reported similar import problems.

@sstlaurent, your AFPUB file does not download. Maybe because of the accented letter in the name?

@Old Bruce, if you import an Excel file, it is to save time, not to lose time converting it into this or that!

 

[Pauls]

Can you try deleting then reattaching the data source in the afpub file and seeing if that corrects the problem

[sstlaurent]

thank you all. Replies to your comments:

I've tried numerous imports with variations::

• AFPUB with no accents, with different name
• save the data file xlsx with a different name
• Inverse column B with A (the problem is not the column but the date)
• put a different year-date
• Write real values in all column formated as 2023-01-01, OR put formulas like B=A+1, C=B+1, etc.)

in all cases, I get minus 1 for the date written in YYYY-MM-DD in every column.

I GET GOOD RESULTS if I use the format which is not our standard in French: DD-MM-YYYY.

if I import the datas in CSV, the A value is EXACT in AFPUB but I don't find CSV option as interesting.

[Antoon Naus]

If encounter a similar problem when merging from an Excel-file. I'm using a time like '20:15' which is imported in the Publisher document as '20:34'. It adds 19 minutes to the input. When I reduce all times in my Excel file with 19 minutes then the data merge result is exactly what the result ought to be. It puzzles me though where this deviation comes from and how it can be corrected. Dates are correctly imported. Working with MacOS.

Edited January 30, 2023 by Antoon Naus
to complete details and working area

[sstlaurent]

I did the same test again this morning (see image). This time, added a new column because your test Anto was done with a slash (/) instead of hyphen (-) like we use in French - the problem I reported is still there:

• YYYY-MM-DD is not recognize, gives a value minus 1
• DD-MM-YYYY is ok
• YYYY/MM/DD is ok

[Pyanepsion]

The date is correct. The behaviour of the generation routine is not.

It is, of course, important to maintain the consistency of the document by ensuring that the values displayed are clear and consistent. It is best to increase the calendar document by document to avoid contradictions. This ensures that the information is always displayed clearly and consistently.

To reproduce the bug related to the increase of values in the document, the following steps should be taken:

1. Load the EPUB and XLSX files which contain 8 identical blocks with 3 fields.
2. Observe that the values are displayed 8 times identically. This is the expected behaviour.
3. Generate the calendar to see that each of the blocks is increased by one day. We should have obtained 28 sheets from 2 to 29 January, each successive date sheet containing the same date 8 times.
4. Observe the contradiction between the two behaviours.

By following these steps, you can reproduce the bug and understand it in more detail.