0

My data is organized by: Day, Month, Year and I'd like it to be grouped by Month, Year so as to properly have it summed up in a pivot.

However, when setting the date to mmm, yy format, the pivot still acts as if there are individual days, so my pivot filter has 30 cases of Apr, 16.

If I do this by converting date to text with =text(B2,"mmm, yy") then the chronological order is lost in the pivot and all I get is some phony alphabetical order by months, and using "mm" instead is no better (the data gets ordered 1, 10, 11, 12, 2, 3...9).

My current formula is =date(right(B2,4)&left(B2,2),1)

where B column has =text(X2,"mm - yyyy")

where X column has dates by day

I'm seeking a way to do this without helper columns and seemingly redundant operations like the formula below:

=date(right(text(X2,"mm - yyyy"),4),left(text(X2,"mm - yyyy"),2),1)

David Andrei Ned
  • 799
  • 1
  • 11
  • 28
  • 1
    Keep dates as such and don't break them down into month, year, quarter, day, etc. Then a standard pivot table in Excel can automatically aggregate your data by year, month, quarter, day, hour, minute, etc. Have a look at the following screencast: http://stackoverflow.com/questions/39459706/sql-server-datetime-object-persistent-reformatting-issue-in-excel/39469650#39469650 Towards the end of the screencast a standard pivot table is inserted. As you can see, Excel automatically recognized the date column and offers aggregation by year and month (which is further customizable). – Ralph Sep 21 '16 at 11:29
  • Sounds lovely but does not work for me (Excel 2010) – David Andrei Ned Sep 28 '16 at 11:35
  • Maybe you can elaborate why this is not working for you? Being a bit more specific might help us resolve the issue for you. BTW, this works in Excel since Version 2007: https://support.office.com/en-us/article/Group-or-ungroup-data-in-a-PivotTable-report-c9d1ddd0-6580-47d1-82bc-c84a5a340725 OR https://www.pryor.com/blog/grouping-excel-pivottable-data-by-month/ OR https://www.youtube.com/watch?v=kntVG8BaCdg – Ralph Sep 28 '16 at 12:03
  • There's no prompt which offers aggregation as you mentioned. However, I've resolved the issue with the formula mentioned at the end of my post, although with constantly updating tables, it's still somewhat of a burden to overwrite it as it constantly gets "Repaired" out of my workbook. – David Andrei Ned Sep 28 '16 at 12:05
  • If no aggregation is offered (as shown in the multiple websites I linked above) then your dates are not really dates but merely seem to be dates (look like dates). For more information you might want to read this: http://stackoverflow.com/questions/37100821/change-date-format-using-substitute-or-replace/37101358#37101358 AND http://stackoverflow.com/questions/38000194/difference-between-date-and-time-w-out-work-week-excel/38001028#38001028 – Ralph Sep 28 '16 at 12:09

1 Answers1

0

=date(right(text(X2,"mm - yyyy"),4),left(text(X2,"mm - yyyy"),2),1)

This proved to be the easiest way to resolve this, as ordering them all by 1/mm/yyyy made it possible to group them up in a pivot, while changing the formatting to "mm - yyyy" made my pivots dates show up as intended

David Andrei Ned
  • 799
  • 1
  • 11
  • 28