See all Microsoft Excel tips
Strategy: You can use a numeric format to force dates to display the month instead of the actual date.
1) Select the range of dates. If you have thousands of rows of data, here is one shortcut. Put the cell pointer in A2. Hit the End key. Hold down Shift while you hit the Down Arrow.
2) From the menu, select Format – Cells (or hit Ctrl+1). In the Format Cells dialog, choose the Number tab.
3) In the Category list box, choose Date.
4) In the Type list box, scroll through and find the format for either “Mar-01” or “March-01”. Select the desired format and choose OK, as shown in Fig. 530.
Result: The daily dates will appear as monthly dates, as shown in Fig. 531.
This tip is fine for printing and even for doing automatic subtotals. It will not work for sorting, formulas, or pivot tables. See the next tip for more details if you need to actually transform the column into months.
Additional Details: If you need to display the month with a four-digit date, you will have to use a custom number format. In the Format Cells dialog, use the Custom category, as shown in Fig. 532. In the type box, use one of these formats:
• mmm yyyy for a format like “Mar 2005”
• mmmm yyyy for a format like “March 2005”
Summary: Use a custom number format to make daily dates appear as monthly dates if you are printing or doing automatic subtotals. Do not use it if you want to sort or use the dates in formulas.
Commands Discussed: Format – Cells – Number
See all Microsoft Excel tips
