Different Ways You Can Use the Excel MONTH Formula
By Indeed Editorial Team
Published 16 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.
Excel has a number of formulas and functions that allow you to manipulate spreadsheet data, including the Excel MONTH formula. This formula lets you extract, convert or manipulate date information, in particular month data. Learning more about how to use this function can be valuable if you use Excel regularly. In this article, we go through a list of examples showing different ways you can use the Excel MONTH formula.
Different ways to use the Excel MONTH formula
When using Excel, one formula you can use is the Excel MONTH formula. There are a number of useful ways to use month formulas to lookup, convert or manipulate month information in a spreadsheet. Here are a few examples of useful ways you can use some of these formulas in Excel:
Extracting the month number
It's possible to return the month value from a date in Excel. You can use the MONTH function to return a value for the month from a date entered into a spreadsheet. The number value ranges from "1" for January to "12" for December. The syntax for entering a formula to use this function is "=MONTH(serial_number)" in the formula tab, with "serial_number" being any valid date you're trying to extract the month value for. Here are a few ways you can use the MONTH function to extract a month value:
DATE function: In Excel, you can enter a date using the DATE(year,month,day) function. Entering the formula "=MONTH(DATE(2022,5,6)" for 6 May, 2021 returns a month value of "5".
Using text: Entering a formula for a date you type out also returns a corresponding month value. For example, entering "=MONTH("1-May-2022")" outputs "5".
Cell values: If you have a spreadsheet with date values entered, you can extract the month value from figures in a specific cell. For example, if you have "6 May 2022" in cell "B4", using the formula "=MONTH(B4)" returns the month value for the date in cell "B4", being "5".
Return value for today's date: It's also possible to return a month value for the current date. The formula "=MONTH(TODAY())" returns a value for the current month.
You can also use the "TEXT" function to extract month information from a specific cell as a text string. Entering the formula "=TEXT(B4,"m")" or "=TEXT(B4,"mm")" returns a month number with or without a leading zero, so "1" or "01" respectively. Excel formats these month values as a text string, so this approach may not be appropriate if you wish to use the returned values in further formulas or calculations.
Extracting the month name
You can use Excel's "TEXT" function to return a month name rather than the number. The format of this name depends on the date code you enter into the formula. You can return a three-letter month abbreviation, such as "Jan" for January, from the date in a selected cell. An example formula of this type looks like "=TEXT(B4,"mmm")". You can return a full month name from the date in a selected cell with the following formula "=TEXT(B4,"mmmm")".
Converting a month number to a name
It's possible to convert a number value from "1" to "12" in a cell into a corresponding month name in another cell using the "TEXT" function. You can convert the number into a full month name, abbreviated name or the first letter of the month name, with there also being two different formula approaches to each conversion:
Full month name: Using the "TEXT" function, the formula "=TEXT(B4*28,"mmmm")" returns a month name from January to December for a number value from "1" to "12" in cell "B4". Alternatively, you can achieve the same with a formula "=TEXT(DATE(2022,B4,1),"mmmm")".
Abbreviated month name: Using the "TEXT" function, the formula "=TEXT(B4*28, "mmm")" returns an abbreviated month name, such as "Jan" for January, for a number value from "1" to "12" in cell "B4". You can also achieve this with the formula "=TEXT(DATE(2022,B4,1), "mmm").
First letter of month name: Using the "TEXT" function, the formula "=TEXT(B4*28,"mmmmm")" returns the first letter of the month name for a number value from "1" to "12" in cell "B4". Alternatively, you can achieve the same with a formula like "=TEXT(DATE(2022,B4,1),"mmmmm")".
The first formula approach in each of these examples works because Excel interprets the number one in a list from one to 12 as "1 January 1900". Multiplying the number by 28 gives a month value. For example, multiplying one, two or three by 28 gives days 28, 56 and 84 in the year, which fall in January, February and Match respectively.
Converting month names to a number
Excel uses two functions to allow you to convert month names to corresponding numbers. These functions are "DATEVALUE" and "MONTH". "DATEVALUE" takes a date input as text and converts it to a serial number Excel recognises as a date. "MONTH" then extracts a corresponding month number from this date. The formula for converting a month's name in cell "B4" into a number looks like "=MONTH(DATEVALUE(B4&"1"))". The &"1" part of the formula is informing Excel that the text being converted is a date.
Output the first day of a month
There are two ways to output the first day of a given month and both involve using the "DATE" function. If you have the month number, you can use "=DATE(year,[month number],1)". For example, entering the formula "=DATE(2022,5,1)" populates a cell with the date "1 May 2022". The format of this date depends on how you've formatted the cell. The date may populate as "1-May-22". Another approach returns the first day of the month based on date information in another cell.
This approach uses both the DATE and MONTH functions. The overall format of the formula is ""=DATE(year,MONTH(cell number),1)", with the cell number being for a cell containing a date. For example, if cell "B4" contains the date "06/05/2022", entering the formula "=DATE(2022, MONTH(B4),1)" in another cell may populate that cell with the date "1 May 2022", but the specific date layout depends on the format of the cell.
Finding the last day of a month
You can use Excel's EOMONTH function to find the last day of a month. There are two approaches you can take, either referencing a cell with date information or entering it into the formula manually. Both formulas include an additional factor, which is the number of months before or after the date in the formula you want to get the end date for. Use positive values for future dates and negative values for past dates.
For example, if cell "B4" has a date entered, the formula "=EOMONTH(B4,1)" returns the last day of the month one month after the date in "B4". A value of "-1" in the formula returns the last day of the month prior. If entering a date value yourself, the formula "=EOMONTH("12-May-2022",0)" returns the last day in May. The EOMONTH function generates a serial number representing a date by default, so it's important to reformat the values into an appropriate date format.
Identifying months or dates within a series
You can identify dates in a spreadsheet within the current month. If you have a list of date information in column "A" from cell "A3" down, it's possible to use the basic DATE formula to find every entry in the column with the same month as the current date by entering "=MONTH($A3)=MONTH(TODAY())" in a separate cell, such as cell "B3" and dragging down column "B". This compares the month numbers from the dates in column "A" against the month of the current date, with the formula returning "TRUE" if they match, "FALSE" if they don't.
Excel uses the date from your device's settings as a reference for the current date. It's also possible to identify dates by month and day. This can be useful to highlight important holiday dates within a series regardless of the year. This is possible using the "MONTH" function to extract the desired month and the "DAY" function for the day. For example, you can highlight Christmas day using a rule that extracts month and day information from the column "A" dates and checks if they equal "12" and "25" respectively. This rule looks like "=AND(DAY($A2)=25,MONTH(A2)= 2)".
Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.
Explore more articles
- Please Do Not Hesitate to Contact Me Sign-off (With Examples)
- Tableau Certification: Definition, Types and Careers That Use It
- What Is the Function of a Manager? A Definitive Guide
- 10 Effective Time Management Skills and Their Benefits
- How to Write a Sincere Thank-You (With Tips and Examples)
- 11 Essential Business Skills To Help Grow Your Career
- How to Say Thank You in an Email (Plus a Few Examples)
- Goodbye Message: Why It's Important and Several Examples
- What Is a Brokerage Account? (With Benefits and FAQs)
- What Is Attention to Details? Definition, Examples and Tips
- What Is Creative Thinking and Why Is It Important?
- What Is Overtime Work? (With Tips and Calculations)