Excel: Display Monthly Dates

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


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.

  1. 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.

  2. Excel displays the daily dates as months.