Is there a way to alter value in pivot table cell using excel functions like CONCATENATE or TEXT?

Question

So in my spreadsheet i need to group data by weeks. Also there is a specific requirement to display weeks on graphs not in full format, but like this "11-17.03"

Weeks on graph

For this i modified the SQL query to not only get dates, but also the dates of the end of the week and then in excel on the left of the pivot table calculated week start using this formula

=DATE(YEAR(H5); MONTH(H5); DAY(H5) - 6)

Week start and week end

And to display desired format for the weeks on graph i created additional table under the graph, where most of the data is copied from original pivot table, but date is modified using the known week start and week end with formula

=(CONCATENATE(LEFT(TEXT(G5;"dd.mm.yyyy"); 2);"-";MID(TEXT(H5;"dd.mm.yyyy");1;5)))

This was working fine, but then there was an additional requirement with another table, where date is not in rows, but in columns and table is not a fixed size, which is where my approach falls.

I tried to format the weeks in SQL query, but then i cannot order them as dates in excel - they become text and 25-31.03 will go after 01-07.04 and totally break the order, so then someone needs to place everything by hand. SQL query for weeks

Hence the question - is there a way, to modify a row or a column in pivot table, that displays week end like "17.03.2024" using some formulas stated above, for it to look like "11-17.03"? Or maybe even another approach that will help me achieve my goal?

Answer 1

Let's say you got data like this:

As you can see, I've added End week to force Pivot Table to order results properly and then the graph shows both fields (End week and Custom Format)

Now apply format ;;; to field End week and let the magic happens in the graph:

You see no values but they are still there, in fact you can sort backwards if you need to:

This way you sort data using an invisible field in the graph. Notice this field is still visible on the pivot table tough

UPDATED: Adding pivot table setup

To add a field anywere, just click & drag it.

To modify format on a field, click on the little triangle at right (do not apply format to cells, apply it to field)