How to use an Excel month formula (guide and FAQs)
By Indeed Editorial Team
Updated 4 November 2022
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.
In spreadsheet software like Excel, there are many features that make performing certain tasks easier. These include things like functions and formulas for quickly deriving information such as a month formula. If you use Excel a lot in your work or expect to do so, then understanding how to find the month with an Excel formula can prove useful. In this article, we explain what Excel formulas are, how to implement an Excel month formula and answer some frequently asked questions.
What are Excel formulas?
A formula in Microsoft Excel is a calculation the software application performs for you. For instance, you may wish to add the values for two cells and present the result in a third one. A formula always starts with an equals (=) sign. You can also use functions within these formulas, which are pre-designed formulas for performing specific calculations. For instance, you could use '=AVERAGE()' to find the mean average value for a group of entries.
Typically, you'd enclose the input information for a function in brackets, giving you an overall formula that Excel can understand and calculate for you. For example, assume you had a column of entries from B2 to B20 and wanted to find the sum total. In this case, you could use the 'SUM' function. You'd do this by typing '=SUM' into an appropriate cell and then clicking and dragging over the column of entries you want. Alternatively, you could manually type out the entire formula, with the cell range separated by a colon and including the SUM function, as follows:
Related: 15 basic Excel formulas to learn
How to implement an Excel month formula
An Excel month formula is one where you use the MONTH function to extract the month from a date. This is useful when you have a column that contains a day, month and year, and you want to separate the data by months. You can use this formula rather than extracting and typing in the information manually. This function always produces a number to represent the month, such as 11 for November. There are different ways of doing this, depending on the format of the original date, which you can find below:
1. Extract the month number from a serial number
One of the simplest ways is when the date entries appear in the form of a serial number. In this case, you use the MONTH function and enter the serial number between brackets after it. An example would look like this:
2. Extract the month number from a cell
When there is a column or row of entries you want to break down into months, you can use simple cell names. In Excel, a cell name is a combination of a letter and number, which you can see as the horizontal and vertical axes of the sheet respectively. You can find a cell's name by clicking on it and seeing the letter and number highlighted.
To use this with the MONTH function, type in the name of the cell within brackets after the function. Alternatively, type the function, open brackets, click on the cell you want to use, close the brackets and then press enter. If you want Excel to perform this formula for every entry in the column, double-click on the small square in the bottom-right corner of the new cell. If you only want Excel to do this for a certain number of cells in the column, click and drag this small square down to the appropriate cell. The formula would like this:
3. Extract the month number from a date
There are two ways of extracting the month number from a date depending on the date format you want to use. The first is to give the date in a format Excel clearly understands and the second is to use quotation marks to indicate to Excel that the enclosed information is a date. These are as follows:
Getting the month name
The previous method provides you with the month as a number between 1 and 12. In some cases, you may wish to get the month as a name such as March or November. In this case, you can use the TEXT function in your formula instead. You can get this as a full name or as a three-letter abbreviation, such as Jan or Feb. Alternatively, you could even get the first letter of month's name, although this could be confusing as some of them share the same first letters, such as March and May or August and April.
To do this, you type in '=TEXT()' and add the relevant cell number within the brackets, along with "mmm" or "mmmm" after a comma. Three m's gives you the month as a three-letter abbreviated name, while the four m's gives you the month as a full name. You could also use five m's to get the first letter of the month's name. Alternatively, you could type out the dates using the same formats as before instead of a cell number. Some examples of these are as follows:
Frequently asked questions
Below are some frequently asked questions and answers about the month function and dates in Excel:
How do serial numbers work?
In Excel, a serial number or serial date is a method whereby a specific day correlates to a specific number. This number represents the number of days since the start of the 20th century. For example, a serial number of 1 means the 1st January 1900. The 1st May 2021 is 44317, because it's 44,316 days after the 1st January 1990.
If you want to convert a column of date values into serial numbers, there's a simple way to do so. First, highlight the column or row in question. Then, look at the 'Number' section under the 'Home' tab in Excel. The drop-down box displays the word 'Date' if Excel recognises the entries in this column as such. In this case, click on this drop-down box and select 'General'. This converts the date entries into serial numbers. You can also use the DATEVALUE function to convert a text-based date into a serial number, as follows:
How can I convert a month name into a number?
If you have a series of entries that show the months as names, you might prefer to display them as numbers. In this case, there's a formula you can use to convert the names to numbers which uses two functions. These are the MONTH function and the DATEVALUE function. In this case, you just require an entry where you can find the name of a month. In the case where the month name is in cell A12, the formula is as follows:
=MONTH(DATEVALUE(A12 & "1"))
If you want to repeat the process for an entire column, double-click on the small square in the bottom-right of the highlighted cell. If you only want a limited number of results, click and drag this small square until you reach the appropriate cell.
How can I get the sum of all results for a given month?
This is a useful application of the MONTH function. If you have a lot of entries, you can get the sum totals per month. This might be useful for something like sales data, for which you might have results on a per-sale basis. In this case, you might have a column of date entries and another column containing the sales or other number for that date. To get monthly totals from this, the first thing to do is use the MONTH function to create a new column whereby every entry has an accompanying month number in the next column.
The second step is to use the SUMIF function, which adds up entries if they meet certain conditions. There are three elements to a SUMIF function, which you add in brackets after it. In order, these are the range, the criteria and the sum range. The range is the month number column, the criteria is the month number for which you want results and the sum range is the sales entries you want to add. So, if you wanted all of the results for March and have month numbers in column E and sales figures in column D, you'd type:
=SUMIF(E15:E28, 3, D15:D28)
Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.
Explore more articles
- What is a zero-coupon bond? (Benefits and examples)
- How to edit your podcast (with the 3 best methods)
- How to build a successful middle management career
- What is single customer view? (Types and benefits)
- Step-by-step guide to making an invoice design template
- 22 sales strategy examples to help you perfect your process
- 8 inclusion strategies in the workplace (plus benefits)
- Benefits of Zoom icebreakers and 11 virtual activities
- What are the 4 Ps of marketing? (and how to use them)
- A quick guide to the pros and cons of mass marketing
- What is a user requirements specification? (Plus elements)
- 12 email management tools (plus definition and uses)