Min/Max of Date in Pivot Table doesn't work

[Gene]

Loved the latest tip from Chandoo on using Min and Max for dates in a pivot table! Problem is - I have files where sometimes this works and other times I get an error saying the pivot table cannot use Min/Max for the values.

I'm stumped as to why. Any ideas? A small sample file is attached.

Many many thanks!
Gino

 

[Chihiro]

Min or Max isn't supported for Date Data types. What you can do is convert the date column to General type (cell format). Then refresh Pivot Table and adjust Number Format to Date type there.

See attached.

 

[Gene]

Thanks so much Chihiro! I was formatting the source as the date instead of the other way around! Much appreciated!
Cheers,
Gino

 

[vletm]

Maybe Excel versions work different way?
I tested to make a Pivot-table and
'just' add 'Min/Max'-date and select to show those with date-format without any converting...

 

[Chihiro]

Likely. I just tested on Excel 2010 and no conversion needed (has PowerPivot & PowerQuery along with MySQL for Excel add-in).

While for Excel 2016 I needed to do conversion (has Solver Add-in, MySQL for Excel, Erlang for Excel).

Edit: I'll test it on Excel 2013 once I get home.

 

[vletm]

hmm ... with 2011 version (without any of those You named) it worked.
Okay!

 

[Chihiro]

Ah, found the culprit.

The pivot table in Op's sheet is created via Data Model. With this set up, you either need to conversion after it's been loaded to PivotTable or there's additional transformation needed within Data Model.

If simple Pivot Table straight from sheet data, this additional conversion isn't needed.

 

[avanv]

Ah, found the culprit.

The pivot table in Op's sheet is created via Data Model. With this set up, you either need to conversion after it's been loaded to PivotTable or there's additional transformation needed within Data Model.

If simple Pivot Table straight from sheet data, this additional conversion isn't needed.

Can you please elaborate on how to "convert a pivot table after it's been loaded?" I have a pivot table that is based on two data sources (worksheets); they are linked by a common field. I am not able to do calculated fields in the resulting pivot table but, of course, must be able to.

 

[Chihiro]

This is 4 year old question... a lot has changed since then (Data model has gone through several changes).

I'd recommend that you start new thread with your specific example (sample file with raw data/model and your desired result. Result should be manually created, if necessary).