Convert Excel Days To Months

November 21, 1998 - by Bill Jelen

Kevin at Amerimail.com 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.

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.