MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Convert Excel Days To Months

November 21, 1998 - by Bill Jelen

Kevin at sent in this week's Excel problem.

I have a table of events in Excel. Each row has an Excel date indicating the day that the event occurred. I want to use an Excel 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.


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

Converting Days to Months

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.

Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.