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

Kashflow logo
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

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.


Please login or register to join the discussion.

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