How to add a month column in an Excel worksheet?

User Question

I have an Excel 365 spreadsheet tracking customer orders with transaction dates, but I need to analyze sales trends by month using Microsoft Excel. The dates are in a single column, but I’m not sure how to extract just the month. How can I create a new column that shows only the month for each transaction date, preferably displaying the month name (like “January”) instead of a number? Can you provide an example with some sample data?

Sample Data

Here’s a tab-separated sample dataset to work with in this tutorial

ID    Date    Product Amount
1001    1/15/2024   Laptop  999.99
1002    2/3/2024    Smartphone  599.99
1003    2/17/2024   Headphones  149.99
1004    3/5/2024    Tablet  449.99
1005    3/22/2024   Smartwatch  299.99
1006    4/8/2024    Camera  799.99

Step-by-Step Solution

  1. Open a new Excel spreadsheet.
  2. Copy and paste the sample data into your spreadsheet, starting from cell A1.
  3. Go to the Data tab. Make sure to use “Text to Columns” with space as the delimiter to separate the data into columns properly.
  1. Your data should now occupy columns A through D. Column B contains the transaction dates.
  2. Click on the header of column E (which should be empty) to select it.
  3. In cell E1, type “Month” as the header for our new column that we will be adding into the spreadsheet.
  4. In cell E2, enter the following formula:
   =TEXT(B2, "mmmm")
  1. Press Enter. This will display “January” for the date in B2.
  2. To apply this to all rows, hover your cursor over the bottom-right corner of cell E2 until you see a small black cross.
  3. Double-click this cross to auto-fill the formula down the entire column.
  4. Your spreadsheet should now have a new “Month” column, displaying the month name for each transaction date.

Explanation

  • The TEXT function in Excel allows us to format dates and numbers as text.
  • The first argument (B2) refers to the cell containing our date.
  • The second argument (“mmmm”) is a date format code that tells Excel to display the full month name.

If you prefer a shorter month name or just the number, you can modify the format code:

  • For short month names (Jan, Feb, etc.): Use “mmm”
  • For month numbers: Use “m” (1-12) or “mm” (01-12)

Can’t see the Month column in Excel

  1. If you see ##### in cells, the column might be too narrow. Simply widen the column.
  2. If the formula returns #VALUE!, ensure your date column actually contains valid dates.
  3. For dates not recognized by Excel, use the DATEVALUE function first:
   =TEXT(DATEVALUE(B2), "mmmm")