How to Use the Excel MONTH Formula (Plus Alternatives)
By Indeed Editorial Team
Published 6 May 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
You can perform various functions on Excel such as organising, formatting and calculating data with formulas. The MONTH function enables you to display month numbers and names. Learning how to use the MONTH function can help you modify dates so that you can extract data from a column easily. In this article, we explore the definition of the Excel MONTH formula, outline how you can use the function and provide alternatives.
What is the Excel MONTH formula?
The Excel MONTH formula is a type of function under the 'Date/time' category. The function allows you to extract information about the month. For instance, if you had a large data set showing dates when the company purchased certain items, you can get a general idea of the number of orders per month. When you use the month function, Excel returns a number corresponding to the month number. For example, if you add the date 28 August 2019, Excel displays 8 as the month. Here's the syntax for the month function:
= MONTH (serial_number)
How to use the MONTH function
Here are four steps you can follow to use the MONTH function:
1. Add dates using the date function
When adding a date to Excel, it's important to ensure it's after 1 January 1900. For the MONTH function to work, Excel may require you to add dates by using the DATE function. Before using the function, you can create separate rows for the year, month and day. Once you input the data in each column, you can add the DATE function to the formula bar. Here's the syntax for the DATE function:
=DATE(year, month, day)
Assuming you added the year, month and day to cells A3, B3 and C3, respectively, you can type the function in cell D3. This prompts Excel to use column D to display the full date. Here's how the formula may appear on the formula bar:
2. Use the MONTH function
To extract the month names from column D, you can create a column for the month numbers. Next, click on a cell where Excel may add the numbers and type '=MONTH' on the formula bar. You can then select a cell that contains the date. In this case, it may be a cell in column D, such as cell D3. You can complete the formula by adding closing parentheses and clicking 'Enter' on the keyboard.
Excel then displays the month number on the cell you selected. For instance, if the date on cell D3 is 2022/2/4, Excel inputs 2. To add month numbers for the rest of the dates, you can select the first cell that contains a month number and click the bottom right corner of the cell. As you click, drag the mouse down and Excel applies the function to the rest of the cells to display all the month numbers.
3. Create and name a range
To change the month number to text, you can first create a separate column of cells with month numbers and the corresponding month name. Excel can then compare the month numbers from step two to the range and input the month's name. You can create the range on a different worksheet. To begin, select a cell and type '1' into it and then click on 'Enter'.
Excel moves the cursor to the next cell, where you can type '2'. Next, highlight both cells and drag the highlight until row 12 for Excel to add the rest of the month numbers. In the next column, add the month names. For example, next to the cell that contains 1, type January. Once you add all the months, you can name the range by typing Months on the name box.
4. Change the month numbers to text
Next, return to the original worksheet and create a column for Month name. You can then select the cell next to the first month number and type the VLOOKUP function. You can complete the function by adding the cell reference of the cell with the month's number, the range name and the number of the column in the range that you plan to display. Next, you can press 'Enter' and Excel displays the month's name. You can drag the fill handle down the column to apply the function to the rest of the cells.
For example, if you're typing the formula in cell E3 with the month number in cell D3 and the second column in the range is the one with month names, you can type the following formula:
=VLOOKUP (D3, Months,2)
Alternatives to the MONTH function
Here are alternative Excel tools you can use to display the month's name:
Full date format
If you prefer to skim the dates, you can use the full data format. For example, for a date that appears as 1/1/2022, Excel changes it to 1 January 2022. To change the date format, you can first highlight the column or row that contains the dates. Next, navigate to the menu bar, click on the 'Home' tab and move to the 'Numbers' section. Under the 'Numbers' section, there's a drop-down you can click then select 'Long date'. Excel then changes the format to the full date format so that the month's name is visible.
Custom date format
This alternative can help you display the month name just like the MONTH function. To use this, you can select all the cells that contain the date. Next, right-click on one cell and a menu appears where you can choose 'Format cells'. To open the 'Format cells' option, you can also click 'Ctrl+1' on the keyboard.
When the 'Format cells' window appears, you can click on the 'Number' tab. Next, under the 'Category' section, choose 'Custom'. On the 'Type' text box, add 'mmmm' and then click on 'OK'. Excel may format the date to display only the month's name without the day and year. For instance, for 1/4/2022, Excel displays April.
Flash fill tool
The flash fill feature can detect patterns in a string of data and then complete the pattern for you. For instance, if you have a column of dates and begin typing the month names on a separate column, Excel can detect the action and provide the rest of the names. To activate flash fill, you can type the month names for the first two dates on the adjacent column. For example, if you're adding the month name on column C, you can add names to C1 and C2. When you type the month's name in C3, the rest may appear grey.
You can accept the suggestions by clicking 'Enter' on the keyboard. If month names don't appear in grey as you're typing, you can try to activate flash fill from the menu bar. Select the 'Data' tab and under the 'Data tools' section, click on 'Flash fill' to activate it manually. You can also press 'Ctrl+E' on the keyboard to activate flash fill. To keep the tool active throughout, you can click on 'Tools' then select 'Options'. Next, select the 'Advanced' button and click on 'Editing options'. Finally, click on the checkbox next to 'Automatically Flash fill'.
The TEXT function allows you to change the date to text. The formula contains a 'Value' section where you can insert the cell reference for the date you plan to modify. The 'Format_text' is the format you plan to change the date to and since in this case you may like to display only the month name, it can be m, mm, mmm or mmmm. Here's the syntax for the TEXT function:
= TEXT(value, format_text)
M causes Excel to display the month number without a leading zero, mm shows the month number with a leading zero and mmm produces the abbreviated month name. Alternatively, you can type in mmmm under the 'Format_text' section if you plan to generate the full month's name. To use the TEXT function, you can type it on the formula bar and click 'Enter'. For instance, if you like to change the date format in cell D2 to a full month name, you can type the following formula:
The power query is a tool that allows you to import and transform the appearance of data. To change the date format, you can first create a table from the Excel data. To transform the data into a table, you can click on any cell and select the 'Insert' tab on the menu bar. Under the 'Tables' section, select 'Table'. A 'Create table' dialogue appears where you can input the range of cells that contain the dates. Next, click 'OK' to create a table.
Once you create the table, select it and click on the 'Data' tab on the menu bar. Under the 'Get and transform data' section, select 'From table/range'. The power query editor opens and you can select the range of cells that contain the dates. Next, click on the 'Transform' tab and under the 'Date and time' section, select 'Date'. A drop-down menu appears and you can click on 'Month' then 'Name of month'. The dates then change to the month's name. To finish, click on the 'Home' tab, then select 'Close and load'.
Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.
Explore more articles
- How to Build a Skills Passport in 4 Steps (Including Tips)
- 13 Characteristics of a Professional (Plus Definition)
- Tips for Sharing a Workspace with Housemates
- Learn More About What Makes a Good Marketing Director
- What Is a Virtual Meeting? (Benefits and Platforms)
- What Is Problem-Based Learning? (With Tips and Examples)
- How to Write a Use Case in 8 Steps (Plus Components)
- What Is a Certificate of Incorporation? (And How to Get One)
- Knowledge Sharing at Work (With Strategies and Benefits)
- Key Factors for a Successful Leadership Development Programme
- Streams of Revenue: Definition, Uses, Types and Examples
- What Is Hazard Identification? (Plus Tips for Doing It)