Placing cells with dates

[Kelly Bellis]

Hello,

I've likely overlooked something obvious, or perhaps it's an obscure setting tucked away, that has to do with how AfPub2 (v2.0.4 on Windows 10) is showing TIME-related fields.

The above is how AfPub2 shows cells that are formatted in Excel (& LibreOffice Calc) as Time; i.e., below:

 

[v_kyr]

Hmm, can't reproduce that formating for the time column under APub1 MacOS here when placed in from an xlxs file ...

... somebody with APub2 on Win has to tryout how it behaves there as default.

 

[walt.farrell]

48 minutes ago, v_kyr said:

can't reproduce that formating for the time column

Is the problem with the Time column, or with the weird dates? How does 04/09/23 become 16/09/23, for example? Or 04/10/23 become 16/20/23? Or 04/11/23 become 41/11/23?

[v_kyr]

4 minutes ago, walt.farrell said:

Is the problem with the Time column, or with the weird dates?

AFAI understood the OP ...

Quote

...is showing TIME-related fields.

... so I assumed here values like "0959:30" in contrast to "09:59:30", aka the missing colon seperator!?

[walt.farrell]

That is certainly one oddity in what they showed us, but not the only one

[Pšenda]

20 minutes ago, walt.farrell said:

but not the only one

Yes, but the OP explicitly mentioned the problem with the "TIME-related" function, not with the "DATE-related" function. So it is understandable that v_kyr react to this part of the problem.

[thomaso]

3 hours ago, Kelly Bellis said:

The above is how AfPub2 shows cells that are formatted in Excel

How did you transfer the data from Excel to APub?
Doesn't Excel use a formula for such cell contents as date or time, while each alternatively can be just a number?

 

[Kelly Bellis]

Thank you all very much for responding.

Workflow began with LibreOffice Calc, entering the date and time data into a single cell and formatting the "Start UTC", "Stop UTC", and "Time" cells as Time. The "Time" values are calculated; = (Stop UTC) - (Start UTC)

In AfPub2, Ctrl + Shift M (Place) filtered out the .ods file, so I Saved As the .ods as an .xlsx, and then was able to place the table into the AfPub2 document without issue. Resized to fit. I didn't immediately notice that the time related fields had been chowdered up until I was examining the PDF. I went back and looked at their appearance while in AfPub which, as expected, revealed the issue of messed up values.

For the time being, I think the best the workaround for this issue is to export the table from LibreOffice as a PDF, and then place it into a new AfPub2 document.

@thomaso - Your first question is answered above. Your second question concerning how Excel deals with dates and time, I can't really speak to; however, I know from past experiences in dealing with OpenOffice Calc, and in more recent years, in LibreOffice Calc, that there is an internal procedure, transparent to the user, which converts date and time values into a number. And for it to be properly displayed, an appropriate format gets assigned by the user to the cell. The help page: https://help.libreoffice.org/6.2/en-US/text/scalc/01/04060102.html, explains this more fully, and also offers this notice:

I'm not sure if we can/  nor should we even think of an AfPub2 table as a spreadsheet, nor if the above notice is particularly relevant, but may be worth noting if Serif developers ever decide this issue is worth looking at.

DPOS [broadcast ephemeris] Summary for Project 17U 20230409.xlsx DPOS [broadcast ephemeris] Summary for Project 17U 20230409.ods 17U 20230409.pdf DPOS [broadcast ephemeris] Summary for Project 17U 20230409.pdf

[walt.farrell]

8 minutes ago, Kelly Bellis said:

For the time being, I think the best the workaround for this issue is to export the table from LibreOffice as a PDF, and then place it into a new AfPub2 document.

Or try exporting as CSV.

By the way, I'm not sure that Publisher handles calculations in XLSX times.

[thomaso]

25 minutes ago, walt.farrell said:

By the way, I'm not sure that Publisher handles calculations in XLSX times.

Does APub handle or notice in fact any formula of incoming table cells? If yes, wouldn't it enable us create/enter them, too?

[v_kyr]

For dealing with CSV/TSV files via APub see related ...

• DataMerge
• Populate pages in an instant with Affinity Publisher’s powerful Data Merge Manager
• ... etc. ...

In case of TSV format export (instead of CSV) from LibreOffice Calc see the add-on Calc Macros here ...

• TSV as extension option when saving files
• Have Calc list the option to Save As Tab Separated Values?

 

[thomaso]

1 hour ago, Kelly Bellis said:

I can't really speak to; however, I know from past experiences in dealing with OpenOffice Calc, and in more recent years, in LibreOffice Calc, that there is an internal procedure, transparent to the user, which converts date and time values into a number.

I doubt APub is coded to read / transfer all (some?) possible formulas correctly, otherwise it would enable us to use formulas inside APub (as "spreadsheet" / not "table" only).

Just notice what macOS "Quicklook" (a tiny preview app for multiple file formats) and "BBEdit" display in the Time column K in your .xlsx:

 

[Pšenda]

43 minutes ago, thomaso said:

Just notice what macOS "Quicklook" (a tiny preview app for multiple file formats) and "BBEdit" display in the Time column K in your .xlsx:

 

1 hour ago, Kelly Bellis said:

The "Time" values are calculated; = (Stop UTC) - (Start UTC)

 

[David in Яuislip]

This is with V1, I don't even see the hours in the Time column
So I changed the cell format from [hh]:mm:ss to one from the Excel Format Cells Time category and that's ok as long as the value is <24
N.B. hh:mm:ss doesn't work

Attached file revised as noted, hopefully it'll work

DPOSmine.xlsx

[v_kyr]

1 hour ago, thomaso said:

Just notice what macOS "Quicklook" (a tiny preview app for multiple file formats) and "BBEdit" display in the Time column K in your .xlsx:

And? - As you can see the xml part tells us here ...

-->  <c r="K3" s="8" t="n"><f aca="false">J3-I3</f><v>0.416319444444444</v></c>

... cell = K3; the autocalculation function (J3-I3) for it is set to false; and the cell's value is already given as = 0.416319444444444.

... so there is already a precalculated given value entry for that K3 cell! - As one can also see when opening that file inside some spreedsheet ...

BTW an xlxs file is just a zipped folder/container file with a bunch of xml definition and a xml based worksheet contents files, nothing more. So it can be treated and extracted like a ZIP file and then inspected with whatever editor etc.

[thomaso]

15 minutes ago, v_kyr said:

And?

@v_kyr, @Pšenda, my goal was simply to illustrate two examples of different interpretation + display of the data in the .xlsx.

Accordingly, it was intended as a note to the OP as to why I simply would not expect the file to be imported correctly by APub but save it first in a file without formulas – unless "spreadsheet" functions (formulas) were an existing feature in APub tables. Besides various file formats also within Excel exist workflows to replace formulas by their calculated values.

[v_kyr]

17 minutes ago, thomaso said:

...as to why I simply would not expect the file to be imported correctly by APub but save it first in a file without formulas...

As we don't know what parts the APub XLXS Parser, here it's XML content parser part, reads, skips, or may misinterpret, it's generally overall hard to tell. - However, exporting just final calculated and preformated values in a plain textual manner, should usually work. - So parsing (reading) in instead preformated CSV/TSV data should probably be more hazzle free here in contrast.