MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

Update

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
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.