SOLVED

Grouping QuickBooks transaction dates by month

%3CLINGO-SUB%20id%3D%22lingo-sub-1800516%22%20slang%3D%22en-US%22%3EGrouping%20QuickBooks%20transaction%20dates%20by%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1800516%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20exported%20transaction%20detail%20from%20QuickBooks%20for%20a%20certain%20account%20for%20use%20in%20a%20pivot%20table.%20However%2C%20when%20I%20pull%20the%20date%20field%20into%20the%20column%20portion%20of%20the%20pivot%20table%2C%20I%20cannot%20find%20the%20option%20to%20group%20the%20dates%20by%20month%2Fyear.%20It%20is%20only%20showing%20all%20of%20the%20individual%20transaction%20dates.%20See%20attached%20for%20the%20current%20view%20of%20the%20pivot%20table.%20I%20can%20confirm%20that%20I%20have%20formatted%20the%20source%20data%20for%20this%20column%20as%20%22Date%22%20in%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1800516%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1800757%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20QuickBooks%20transaction%20dates%20by%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1800757%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827443%22%20target%3D%22_blank%22%3E%40egspen2%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20your%20date%20values%20are%20formatted%20as%20text%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1801245%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20QuickBooks%20transaction%20dates%20by%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1801245%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3BThey%20are%20formatted%20as%20dates%20-%20I%20did%20confirm%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1801322%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20QuickBooks%20transaction%20dates%20by%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1801322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827443%22%20target%3D%22_blank%22%3E%40egspen2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20texts%20as%20dates%20isn't%20enough%2C%20you%20shall%20convert%20texts%20to%20dates%20and%20only%20after%20that%20to%20apply%20desired%20date%20format.%20Easiest%20way%20select%20in%20source%20data%20column%20with%20%22dates%22%2C%20Data-%26gt%3BText%20to%20Columns%2C%20on%20third%20step%20of%20the%20wizard%20apply%20Date%20an%20MDY%20(here%20shall%20be%20source%20format%2C%20in%20your%20case%20MDY).%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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.

5 Replies
Highlighted

Hello @egspen2,

 

Perhaps your date values are formatted as text?

Highlighted

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

Highlighted
Best Response confirmed by allyreckerman (Microsoft)
Solution

@egspen2 

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

Highlighted

@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?

Highlighted

@egspen2 

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

image.png

On third stage select Date and MDY

image.png

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

image.png