Using MONTH and EOMONTH functions in Excel - formula examples

Excel MONTH function - syntax and uses

Microsoft Excel provides a special MONTH function to extract a month from date, which returns the month number ranging from 1 (January) to 12 (December).

The MONTH function can be used in all versions of Excel 2016 - 2000 and its syntax is as simple as it can possibly be:

Where serial_number is any valid date of the month you are trying to find.

For the correct work of Excel MONTH formulas, a date should be entered by using the DATE(year, month, day) function. For example, the formula =MONTH(DATE(2015,3,1)) returns 3 since DATE represents the 1st day of March, 2015.

Formulas like =MONTH("1-Mar-2015") also work fine, though problems may occur in more complex scenarios if dates are entered as text.

In practice, instead of specifying a date within the MONTH function, it's more convenient to refer to a cell with a date or supply a date returned by some other function. For example:

=MONTH(A1) - returns the month of a date in cell A1.

=MONTH(TODAY()) - returns the number of the current month.

At first sight, the Excel MONTH function may look plain. But look through the below examples and you will be amazed to know how many useful things it can actually do.

How to get month number from date in Excel

There are several ways to get month from date in Excel. Which one to choose depends on exactly what result you are trying to achieve.

MONTH function in Excel - get month number from date

This is the most obvious and easiest way to convert date to month in Excel. For example:

• =MONTH(A2) - returns the month of a date in cell A2.
• =MONTH(DATE(2015,4,15)) - returns 4 corresponding to April.
• =MONTH("15-Apr-2015") - obviously, returns number 4 too.

TEXT function in Excel - extract month as a text string

An alternative way to get a month number from an Excel date is using the TEXT function:

• =TEXT(A2, "m") - returns a month number without a leading zero, as 1 - 12.
• =TEXT(A2,"mm") - returns a month number with a leading zero, as 01 - 12.

Please be very careful when using TEXT formulas, because they always return month numbers as text strings. So, if you plan to perform some further calculations or use the returned numbers in other formulas, you'd better stick with the Excel MONTH function.

The following screenshot demonstrates the results returned by all of the above formulas. Please notice the right alignment of numbers returned by the MONTH function (cells C2 and C3) as opposed to left-aligned text values returned by the TEXT functions (cells C4 and C5).

How to extract month name from date in Excel

In case you want to get a month name rather than a number, you use the TEXT function again, but with a different date code:

• =TEXT(A2, "mmm") - returns an abbreviated month name, as Jan - Dec.
• =TEXT(A2,"mmmm") - returns a full month name, as January - December.

If you don't actually want to convert date to month in your Excel worksheet, you are just wish to display a month name only instead of the full date, then you don't want any formulas.

Select a cell(s) with dates, press Ctrl+1 to opent the Format Cells dialog. On the Number tab, select Custom and type either "mmm" or "mmmm" in the Type box to display abbreviated or full month names, respectively. In this case, your entries will remain fully functional Excel dates that you can use in calculations and other formulas. For more details about changing the date format, please see Creating a custom date format in Excel.

How to convert month number to month name in Excel

Suppose, you have a list of numbers (1 through 12) in your Excel worksheet that you want to convert to month names. To do this, you can use any of the following formulas:

To return an abbreviated month name (Jan - Dec):

=TEXT(A2*28, "mmm")

=TEXT(DATE(2015, A2, 1), "mmm")

To return a full month name (January - December):

=TEXT(A2*28, "mmmm")

=TEXT(DATE(2015, A2, 1), "mmmm")

In all of the above formulas, A2 is a cell with a month number. And the only real difference between the formulas is the month codes:

• "mmm" - 3-letter abbreviation of the month, such as Jan - Dec
• "mmmm" - month spelled out completely
• "mmmmm" - the first letter of the month name

How to convert month name to number in Excel

There are two Excel functions that can help you convert month names to numbers - DATEVALUE and MONTH. Excel's DATEVALUE function converts a date stored as text to a serial number that Microsoft Excel recognizes as a date. And then, the MONTH function extracts a month number from that date.

The complete formula is as follows:

=MONTH(DATEVALUE(A2 & "1"))

Where A2 in a cell containing the month name you want to turn into a number (&"1" is added for the DATEVALUE function to understand it's a date).

How to get the last day of month in Excel (EOMONTH function)

The EOMONTH function in Excel is used to return the last day of the month based on the specified start date. It has the following arguments, both of which are required:

• Start_date - the starting date or a reference to a cell with the start date.
• Months - the number of months before or after the start date. Use a positive value for future dates and negative value for past dates.

Here are a few EOMONTH formula examples:

=EOMONTH(A2, 1) - returns the last day of the month, one month after the date in cell A2.

=EOMONTH(A2, -1) - returns the last day of the month, one month before the date in cell A2.

Instead of a cell reference, you can hardcode a date in your EOMONTH formula. For example, both of the below formulas return the last day in April.

=EOMONTH("15-Apr-2015", 0)

=EOMONTH(DATE(2015,4,15), 0)

To return the last day of the current month, you use the TODAY() function in the first argument of your EOMONTH formula so that today's date is taken as the start date. And, you put 0 in the months argument because you don't want to change the month either way.

=EOMONTH(TODAY(), 0)

And here are the results returned by the Excel EOMONTH formulas discussed above:

If you want to calculate how many days are left till the end of the current month, you simply subtract the date returned by TODAY() from the date returned by EOMONTH and apply the General format to a cell:

=EOMONTH(TODAY(), 0)-TODAY()

How to find the first day of month in Excel

As you already know, Microsoft Excel provides just one function to return the last day of the month (EOMONTH). When it comes to the first day of the month, there is more than one way to get it.

How to calculate the number of days in a month

In Microsoft Excel, there exist a variety of functions to work with dates and times. However, it lacks a function for calculating the number of days in a given month. So, we'll need to make up for that omission with our own formulas.

How to sum data by month in Excel

In a large table with lots of data, you may often need to get a sum of values for a given month. And this might be a problem if the data was not entered in chronological order.

The easiest solution is to add a helper column with a simple Excel MONTH formula that will convert dates to month numbers. Say, if your dates are in column A, you use =MONTH(A2).

And now, write down a list of numbers (from 1 to 12, or only those month numbers that are of interest to you) in an empty column, and sum values for each month using a SUMIF formula similar to this:

=SUMIF(C2:C15, E2, B2:B15)

Where E2 is the month number.

The following screenshot shows the result of the calculations:

If you'd rather not add a helper column to your Excel sheet, no problem, you can do without it. A bit more trickier SUMPRODUCT function will work a treat:

=SUMPRODUCT((MONTH($A$2:$A$15)=$E2) * ($B$2:$B$15))

Where column A contains dates, column B contains the values to sum and E2 is the month number.

How to conditionally format dates based on month

Now that you know how to use the Excel MONTH and EOMONTH functions to perform various calculations in your worksheets, you may take a step further and improve the visual presentation. For this, we are going to use the capabilities of Excel conditional formatting for dates.

In addition to the examples provided in the above mentioned article, now I will show you how you can quickly highlight all cells or entire rows related to a certain month.