Pivot Tip #30: Use =MONTH and =YEAR functions to summarise dates. By David Carter

Share this content

David Carters series of Five Minute Tips aims to develop your skill at using Excel pivot tables. If you want to test the tip for yourself, David takes you through a worked example on the pivot practice database.

If you want to summarise transactions by month, Group is the obvious way to do it.

But if just one record out of thousands contains a faulty date or blank cell, then Group doesnt work and gives the Cannot Group this selection message.

Personally, I dont use Group any more. A better way is to use the =MONTH and = YEAR functions.

If the Date column is column A, insert two blank columns to the right of it. In B2 type =MONTH(A2), and in C2 type =YEAR (A2).

Both values will initially be displayed as a date. Change the format to Format General.

Practice Session...

Please Login or Register to read the full article


Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.