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
- Open a new Excel spreadsheet.
- Copy and paste the sample data into your spreadsheet, starting from cell A1.
- Go to the Data tab. Make sure to use “Text to Columns” with space as the delimiter to separate the data into columns properly.
- Your data should now occupy columns A through D. Column B contains the transaction dates.
- Click on the header of column E (which should be empty) to select it.
- In cell E1, type “Month” as the header for our new column that we will be adding into the spreadsheet.
- In cell E2, enter the following formula:
=TEXT(B2, "mmmm")
- Press Enter. This will display “January” for the date in B2.
- To apply this to all rows, hover your cursor over the bottom-right corner of cell E2 until you see a small black cross.
- Double-click this cross to auto-fill the formula down the entire column.
- 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
- If you see ##### in cells, the column might be too narrow. Simply widen the column.
- If the formula returns #VALUE!, ensure your date column actually contains valid dates.
- For dates not recognized by Excel, use the DATEVALUE function first:
=TEXT(DATEVALUE(B2), "mmmm")