[Solved] Sheet wExternal Link keeps setting number as a date

[x2rider]

I am linking an external data to a spreadsheet. There is a header row, and the first column just has numbers, starting with 0, then goes up from there.

The problem is, the spreadsheet keeps showing the data as a date instead of a number. If I set the column format to a number, it then shows correctly, but when the data updates it goes back to a date. The sheet will not keep the formatting.

Is there any hint I can put in the data to tell calc that this is a number field, or specify the format in the html table, like a schema specification? I can't seem to find any information on this.

[Bidouille]

As usual, what format has been set your document? xls, ods, other?

[Villeroy]

Also important: What happens exactly? Which value is displayed as which date?

[x2rider]

My file is an ods calc document

I've added data via an external link which is a web page with just a table of data.
The numbers are unsigned integers, so I don't know why it feels that 0 or 14 is a date.

These pics posted in reverse order, so start from the bottom and work your way up.

At this point, I'm programming a macro to run before doing any processing to set the number format to 0

[Villeroy]

Remove the number format from the cells. "3rd Jan 1900" is just the same as 4. It is day number 4. All spreadsheet dates are formatted day numbers actually.

[x2rider]

Ok, maybe I haven't explained it clear enough.

I understand that 4 is Jan 3, 1900, but that's not the point.

I have started with a NEW sheet, and it's doing this.
I have TRIED setting the format to a number to get it to show correctly.
Once the data refreshes, it goes BACK to a default which is shown as a date. The column format by default is NOT set to a date format, it's just keeps showing it as a date.

No matter what I do, the data refresh will reset it back to showing as a date. It needs to be a number so it'll sort correctly and for the macros to work.

[karolus]

Hallo

It look and sounds like you has changed the number-format in your Default-Style??

→[F11]→right-click on Default→modify→look in the Number-Tab and change back to Category:Number and Format:Standard.

[Villeroy]

Hallo

It look and sounds like you has changed the number-format in your Default-Style??

→[F11]→right-click on Default→modify→look in the Number-Tab and change back to Category:Number and Format:Standard.

As long as I am not able to reproduce this strange issue, this is the only thing I can think of. Never encountered any "auto formatting" with links. May be an issue with the source HTML?

[x2rider]

Thank you karolus, I think that resolved it!

It was set to user defined then general. After setting this like you state below, I did a refresh on the data and it stayed a number!

Wow, what a bizarre thing, but I'm new to OpenOffice, I would never have found this. Now I can remove the column formatting code from my macros.

Hallo

It look and sounds like you has changed the number-format in your Default-Style??

→[F11]→right-click on Default→modify→look in the Number-Tab and change back to Category:Number and Format:Standard.