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
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.