Forum Discussion

Copper Contributor

Feb 04, 2021

Pivot Table fields - "Year"" not displaying automatically

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 no...

Excel on Mac

mathetes

Silver Contributor

Feb 04, 2021

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.

SDesmarais

Copper Contributor

Feb 04, 2021

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.

Wyn Hopkins
MVP
Feb 05, 2021
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
Riny_van_Eekelen
Platinum Contributor
Feb 05, 2021
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.
mathetes
Silver Contributor
Feb 05, 2021
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?