barcelona

more panorama pictures

excel

1 click excel macro tool

Categorize days by week/month/year in an excel report

Most of my reports contain a date column, no matter the name because it can be “start date”, “end date”, “update date”, etc. at the end, it is a date. Most of the time, I may need to know how much incidents for a specific day but what about if I want to know how many per week or per month ?

There is a simple way to do it, no need a formula or a macro, I just need to create a pivot table and once done, I just need to use the “group” option.

Imagine that I have such data:

categorize days by week month year

The first step will be to create a pivot table and once done, just right click anywhere in the column A to select “group”.

categorize days by week month year
categorize days by week month year categorize days by week month year

A popup will appear (left picture). As shown in the right picture, I select “days” and in the “number of days” field, I put 7.

categorize days by week month year categorize days by week month year

Once I click on “OK”, this is the result:

categorize days by week month year

As you can see, you can do also by month, by quarter, etc. as simple as by week.

Interesting Management