This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Display Monthly Dates
Problem: I have a data set that shows the actual date for each invoice. When I print the invoice register, I would like to print just the month and year instead of the specific date.
Figure 535 Display daily dates as months in column A.
Strategy: You can use a numeric format to force dates to display the month and year instead of the specific date. Here’s how:
1. Select the range of dates. If you have thousands of rows of data, you can select them all by putting the cell pointer in A2, then pressing Ctrl+Shift+Down Arrow.
2. Press Ctrl+One to display the Format Cells dialog.
3. In the Format Cells dialog, choose the Number tab.
4. In the Category list box, choose Date.
5. In the Type list box, scroll through and select either Mar-01 or March-01. Click OK.
Results:The daily dates will appear as monthly dates.
This process is fine for printing and even for doing automatic subtotals. It will not work for sorting, formulas, or pivot tables. See “Calculate First of Month" for details on actually transforming the column into months.
Figure 536 Excel displays the daily dates as months.