Ask Mr. Excel - Convert daily dates to monthly dates

Back

About MrExcel

Consulting Services

Learn Excel Resources

Challenge of the Month

Past Tip of the Week

11/21/98: Kevin at Amerimail.com sent in this week's problem. I have a table of events in Excel. Each row has a date indicating the day that the event occurred. I want to use a pivot table to summarize the data, but I want to see it at the month level instead of at the daily level. I tried simply formatting the date column to display a month, but the pivot table still breaks the data out by day.

Update: If you are using Excel 97 or higher, the new Excel grouping function will solve this problem with one less step.

Excel stores dates as a number which represents the number of days since 1/1/1900. Your formatting trick was a great try, but it merely changed the way Excel displayed the data. You'll have to insert a new column called Month. Use the Excel DAY() function will tell you the number of days that have elapsed in the current month. So, for 10/2/98, the day() function would return 2. At the left, you can see the new formula. Subtract the numbers of days elapsed from the date, add 1, and you will always have the first day of the month. Enter this formula in B2, double click the fill handle to copy the formula down, and then Format Cells to pick a date format of mmm-yy.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.