Grouping QuickBooks transaction dates by month

Copper Contributor

I have exported transaction detail from QuickBooks for a certain account for use in a pivot table. However, when I pull the date field into the column portion of the pivot table, I cannot find the option to group the dates by month/year. It is only showing all of the individual transaction dates. See attached for the current view of the pivot table. I can confirm that I have formatted the source data for this column as "Date" in Excel.

6 Replies

Hello @egspen2,


Perhaps your date values are formatted as text?

@PReagan They are formatted as dates - I did confirm that.

best response confirmed by allyreckerman (Microsoft)


Format texts as dates isn't enough, you shall convert texts to dates and only after that to apply desired date format. Easiest way select in source data column with "dates", Data->Text to Columns, on third step of the wizard apply Date an MDY (here shall be source format, in your case MDY).

@Sergei Baklan I assume this means do text to columns, delimited by "/", which will split my current "date" column into 3 separate columns (M, D, Y), then I can delete the day column and arrive at what I need? Do I understand you correctly?


Nope. For the column like this you may select Fixed width or no actual delimiter on next step


On third stage select Date and MDY


Result will be the dates in your default date format (I use ISO format)


Upgrade your older version of QuickBooks to the latest QuickBooks Desktop 2023. Unlock new features, improved performance, and advanced financial management capabilities. Upgrade now for seamless accounting experience.