Display Monthly Dates

June 20, 2022 - by Bill Jelen

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.

The date column contains daily dates. You want to subtotal by Month.
Figure 527. 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.

Format the daily dates as Monthly Dates
Figure 528. Excel displays the daily dates as months.

This article is an excerpt from Power Excel With MrExcel

Title photo by Glen Carrie on Unsplash

Bill Jelen is the author / co-author of:
Saving Time with Excel - Online Course

Be more efficient in Excel with these time-saving techniques in Excel.