How to pull the month from date in Excel

You can watch a video tutorial here.

In Excel, you will frequently get to work with dates. Most transactional data such as lists of orders and lists of sales will include the date of the transaction. While it is fine to keep the date in the date format for most reports, you may need to extract the month from a date. For example, if you need to consolidate data for a particular month over 5 years, you will need to know the month to which the data belongs. Excel has functions to perform this operation and you can choose the function according to the output that you want.

Option 1 – Extract the date as a number

Step 1 – Use the MONTH() function

• Place the cursor in the destination cell 
• Type the formula using the cell reference:

=month(Date)

Step 2 – Copy the formula to the rest of the cells and check the result

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

• Check that the month numbers have been extracted for all dates 

Note: When this output is used in calculations, it is treated as a regular number and will not be considered a month i.e. in the date format.

Option 2 – Extract the date as an abbreviated text

Step 1 – Use the TEXT() function

• Place the cursor in the destination cell 
• Type the formula using the cell reference and 3 m’s:

=text(Date,”mmm”)

Note: The 3 m’s indicate that the resulting text should be abbreviated to the first 3 characters of the name of the month

Step 2 – Copy the formula to the rest of the cells and check the result

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

• Check that the month short names have been extracted for all dates

Note: This output is treated as regular text and will not be considered a month i.e. in the date format.

Option 3 – Extract the date as the full name of the month

Step 1 – Use the TEXT() function

• Place the cursor in the destination cell 
• Type the formula using the cell reference and 4 m’s:

=text(Date,”mmmm”)

Note: The 4 m’s indicate that the resulting text should be the full name of the month

Step 2 – Copy the formula to the rest of the cells and check the result

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

• Check that the month full names have been extracted for all dates

Note: This output is treated as regular text and will not be considered a month i.e. in the date format.