How to: Show the date when the max value happened?

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38
Hi,
To keep things simple:
Column A (Date)has the dates
Column B (Value) has the values

I want to find the MAX-value AND the date it happened in pivot tables
Now when I run pivot tables it shows me the year and the quarters.
I don't need the Quarters, so when I deselect this then it shows me the months and the maximum value for that month.
This is a step in the right direction.
I now see the following:
Year, month, max value
My problem is that I would like to see the following:
Year, month, max value and the date it occurred. (Similar to the INDEX function).

Can somebody steer me in the right direction please?
Many thanks in advance!
By the way: I am new to pivot tables, please be gentle:)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38
OK, to make it easier I have copied part of my pivot table:
Pivot table 1.jpg
Pivot table 2.jpg

As you can see I added a column outside the pivot table and used the INDEX function to show the date when the max temperature happened.
Can this be done inside the pivot table itself?
I have tried to insert the 4th column between the 2nd and the 3rd column but the pivot table doesn't allow that to happen.
Hopefully this might be a bit clearer compared to the previous message.
Cheers,
Luke
 

Watch MrExcel Video

Forum statistics

Threads
1,130,117
Messages
5,640,207
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top