Pivot Table fields - "Year"" not displaying automatically

Copper Contributor

Usually when I create a pivot table with a date filed such as "Purchase Date," I automatically get pivot table fields like "Years" and/or "Months" to select in the Pivot Table fields list. This is not happening. What can I do to add "Years" to my list of pivot table fields?

5 Replies
Is your "Purchase Date" field actually an Excel date, or is it text masquerading as a date? If the latter, you should use DATEVALUE to convert it. Then that delightful feature of the Pivot Table should kick in.

@mathetes 

Thanks. If the "Purchase Date" field uses Excel dates, is there a way to click into the Pivot Table fields to create a "Years" or "Months" pivot table field from the "Purchase Date" field? I get this automatically using my Windows Excel version, but on my Mac the same data set doesn't automatically give me a "Years" field when I create a pivot table.

@SDesmarais 

 

I'm on a Mac as well, and whenever there are dates in a field as part of a table that I use as the basis for a Pivot Table, when I use the dates as either the source of the columnar data or the row data, it offers the options of "Year," "Quarter," or "Month" --- I don't need to do anything special to get that.

 

Is it possible for you to post a copy of this errant spreadsheet (so long as the data are not proprietary in some way?

@SDesmarais Check the settings in "Excel", "Preferences...", "Tables and Filters". Make sure that the option at the bottom is NOT checked if you want your dates to be grouped automatically.

Screenshot 2021-02-05 at 09.06.54.png

@SDesmarais 

 

Also in the Windows version you can right click on any date in a Pivot Table (once that date field has been dragged into rows or columns) and pick GROUP

 

Not sure on Mac sorry