Jump to content
You must now use your email address to sign in [click for more info] ×

Error in date imported from XLSX


Recommended Posts

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 by sstlaurent
Link to comment
Share on other sites

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.

Mac Pro (Late 2013) Mac OS 12.7.4 
Affinity Designer 2.4.0 | Affinity Photo 2.4.0 | Affinity Publisher 2.4.0 | Beta versions as they appear.

I have never mastered color management, period, so I cannot help with that.

Link to comment
Share on other sites

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!

 

6 cœurs, 12 processus - Windows 11 pro - 4K - DirectX 12 - Suite universelle Affinity (Affinity  Publisher, Affinity Designer, Affinity Photo).

Mais je vous le demande, peut-on imaginer une police sans sérifs ?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 4 weeks later...

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 by Antoon Naus
to complete details and working area
Link to comment
Share on other sites

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

2023-01-30-bogue-import.jpg

Link to comment
Share on other sites

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.

6 cœurs, 12 processus - Windows 11 pro - 4K - DirectX 12 - Suite universelle Affinity (Affinity  Publisher, Affinity Designer, Affinity Photo).

Mais je vous le demande, peut-on imaginer une police sans sérifs ?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.