Let’s say we have 4 columns: Customer, Order #, Product and Amount
In Order # we have the order number, an integer OR a date if the order is pending until a future date.
If we pivot the table (Customer, Order # and Product as Row, Amount as Data), the Order # column is “promoted” to a date field, turning all the order # numbers to dates (also affects the items if the order # is made a filter to the table, but oddly not when creating a filter criteria)
In fact, if you change the format of one cell of the associated column, the last format set (highest row changed) is the one used to “promote” the entire column.
// I tried inserting images, but new users can’t put more than one embedded media
This looks like a bug (perhaps an oversight)
Maybe we need a way to set (“force”) the data format for each “field” (or at least a “preserve format” option)
As a workaround I know we can set the odd cells to text, but that is not optimal since it requires tweaking the cells and all it takes is to forget to tweak one (or to copy the data from elsewhere and get the wrong format copied)
And before anyone says that you shouldn’t mix dates and numbers, consider the following example: Take the amount column and have cells with a different number of decimal places (1, 2,3 decimal places, even no decimals). See the next capture.
I manually set the format of one cell to 1 decimal, you get
instead of the right values
All you need is copying a number from elsewhere with just the wrong format.
Tested on Calc 7.4.1 and 7.5.1