Inserting a pivot table changes the date format into a number.
This only happens when the date is in “Column Fields”. When I move the date to “Row Fields” it is properly formatted.
Here is my example:
| date | customer | rev |
|---|---|---|
| 01/01/21 | a | 1 |
| 02/01/21 | a | 6 |
| 04/01/21 | c | 9 |
| 05/02/21 | a | 3 |
| 07/02/21 | b | 7 |
| 15/04/21 | a | 12 |
Workaround: After having grouped the dates by month [Data->Group] the date does not lose its format when in
the “Column Fields”.
Version 7.2.3.2
Excellent Vrolik, that solves my problem! But it requires to re-order “Column Fields”. I think the source format must be preserved in a pivot table without any additional steps. Do you agree? Is this a bug? Why does your re-ordering solve my issue?
I observe a loss of date formatting in row and column fields if a database is the source of the pivot table. I can’t reproduce the problem if the source is a spreadsheet range.
My work-around for single days with correct date format is:
