by The tutorial explains the nuts and bolts of Excel MONTH and EOMONTH functions. You will find an array of formula examples demonstrating how to extract month from date in Excel, get the first and last day of the month, convert month name to number and more.
In the previous article, we explored a variety of formulas to calculate weekdays. Today, we are going to operate on a bigger time unit and learn the functions that Microsoft Excel provides for months.
In this tutorial, you will learn:
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 these formulas work
When used together with month format codes such as "mmm" and "mmmm", Excel considers the number 1 as Day 1 in January 1900. Multiplying 1, 2, 3 etc. by 28, you are getting Days 28, 56, 84, etc. of the year 1900, which are in January, February, March, etc. The format code "mmm" or "mmmm" displays only 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)
Note. Since the Excel EOMONTH function returns the serial number representing the date, you have to apply the date format to a cell(s) with your formulas. Please see How to change date format in Excel for the detailed steps.
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.
Example 1. Get the 1st day of month by the month number
If you have the month number, then use a simple DATE formula like this:
For example, =DATE(2015, 4, 1) will return 1-Apr-15.
If your numbers are located in a certain column, say in column A, you can add a cell reference directly in the formula:
=DATE(2015, B2, 1)
Example 2. Get the 1st day of month from a date
If you want to calculate the first day of the month based on a date, you can use the Excel DATE function again, but this time you will also need the MONTH function to extract the month number:
For example, the following formula will return the first day of the month based on the date in cell A2:
=DATE(2015,MONTH(A2),1)
Example 3. Find the first day of month based on the current date
When your calculations are based on today's date, use a liaison of the Excel EOMONTH and TODAY functions:
=EOMONTH(TODAY(),0) +1 - returns the 1st day of the following month.
As you remember, we already used a similar EOMONTH formula to get the last day of the current month. And now, you simply add 1 to that formula to get the first day of the next month.
In a similar manner, you can get the first day of the previous and current month:
=EOMONTH(TODAY(),-2) +1 - returns the 1st day of the previous month.
=EOMONTH(TODAY(),-1) +1 - returns the 1st day of the current month.
You could also use the Excel DATE function to handle this task, though the formulas would be a bit longer. For example, guess what the following formula does?
=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
Yep, it returns the first day of the current month.
And how do you force it to return the first day of the following or previous month? Hands down :) Just add or subtract 1 to/from the current month:
To return the first day of the following month:
=DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1)
To return the first day of the previous month:
=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)
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.
Example 1. To get the number of days based on the month number
If you know the month number, the following DAY / DATE formula will return the number of days in that month:
In the above formula, the DATE function returns the first day of the following month, from which you subtract 1 to get the last day of the month you want. And then, the DAY function converts the date to a day number.
For example, the following formula returns the number of days in April (the 4th month in the year).
=DAY(DATE(2015, 4 +1, 1) -1)
Example 2. To get the number of days in a month based on date
If you don't know a month number but have any date within that month, you can use the YEAR and MONTH functions to extract the year and month number from the date. Just embed them in the DAY / DATE formula discussed in the above example, and it will tell you how many days a given month contains:
=DAY(DATE(YEAR(A2), MONTH(A2) +1, 1) -1)
Where A2 is cell with a date.
Alternatively, you can use a much simpler DAY / EOMONTH formula. As you remember, the Excel EOMONTH function returns the last day of the month, so you don't need any additional calculations:
=DAY(EOMONTH(A1, 0))
The following screenshot demonstrates the results returned by all of the formulas, and as you see they are identical:
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.
Note. Please keep in mind that both of the above solutions add up all values for a given month regardless of the year. So, if your Excel worksheet contains data for several years, all of it will be summed.
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.
Example 1. Highlight dates within the current month
In the table from the previous example, suppose you want to highlight all rows with the current month dates.
First off, you extract the month numbers from dates in column A using the simplest =MONTH($A2) formula. And then, you compare those numbers with the current month returned by =MONTH(TODAY()). As a result, you have the following formula which returns TRUE if the months' numbers match, FALSE otherwise:
=MONTH($A2)=MONTH(TODAY())
Create an Excel conditional formatting rule based on this formula, and your result may resemble the screenshot below (the article was written in April, so all April dates are highlighted).
Example 2. Highlighting dates by month and day
And here's another challenge. Suppose you want to highlight the major holidays in your worksheet regardless of the year. Let's say Christmas and New Year days. How would you approach this task?
Simply use the Excel DAY function to extract the day of the month (1 - 31) and the MONTH function to get the month number, and then check if the DAY is equal to either 25 or 31, and if the MONTH is equal to 12:
=AND(OR(DAY($A2)=25, DAY($A2)=31), MONTH(A2)=12)
This is how the MONTH function in Excel works. It appears to be far more versatile than it looks, huh?
In a couple of the next posts, we are going to calculate weeks and years and hopefully you will learn a few more useful tricks. If you are interested in smaller time units, please check out the previous parts of our Excel Dates series (you will find the links below). I thank you for reading and hope to see you next week!
461 comments
I'm trying to calculate how many months have passed from today's date to a date in the past. (Example: "As of 12/31/2022, how many full months have passed since May 12th, 2022?" Answer "7". Anyone know how to structure that formula?
Hi!
To calculate difference in months, I recommend reading this guide: Excel DATEDIF function to get difference between two dates.
I am looking to have a cell the says January (1) collect data from a date range the cover 01/01/2022 thru 01/31/2022, by specific groups. Can anyone help?
Hi!
You can select data by condition using the FILTER function. Use this instruction: Excel FILTER function - dynamic filtering with formulas.
Hello!
I want to copy month and year of the worksheet "InTime" from the workbook "Time.xlsx" into the worksheet "ActualTime" from the workbook "New_Time.xlsm".
Month and year should be displayed in two different columns.
I did this:
ActiveChart.ChartTitle.Characters.Text = Format(Range("E1").Value, "mmmm")
ActiveChart.ChartTitle.Characters.Text = Format(Range("F1").Value, "yyyy"
Hello Sir,
Thanks for your kind support and guidance as always. I have below date series for the month of Oct'22 & Nov'22 with duplicates dates:
1 24-Nov-22
2 25-Nov-22
3 26-Nov-22
4 27-Nov-22
5 28-Nov-22
6 29-Nov-22
7 29-Nov-22
8 1-Dec-22
9 2-Dec-22
10 3-Dec-22
Now I want to pick the latest date of Nov'22 for the serial 7 (29-Nov-22) although in serial 6 it has same date. Would you please help me.
Thanks in advance.
Mamun
Hello!
To find the last date of the month, search for the last day of the month using the MATCH function with the [match_type]=1 argument. Use the EOMONTH function to determine the last day of the month.
Please try the following formula:
=INDEX(A1:A10,MATCH(EOMONTH(DATE(2022,11,1),0),B1:B10,1))
Pls how can I automate month and year in a loan calculated format in excel.
Hi!
If the advice in the article above doesn't work for you, explain the problem in more detail.
Good day.
Please kindly assist with how to delete month in a bulk dataset of dates and leave only with the year and day only. For example
2022-10-01 to 2022-01
2022-10-02 to 2022-02
2022-10-03 to 2022-03
2022-10-04 to 2022-04
Looking forward to see your reply.
Thanks
Thank you. I have gotten the solution. I’m now thinking how to automate because I have like 4 different excel sheets with almost 1000 data set
Hello, I need help to determine the same day as the end day independently if the Month is 30 or 31 days. So I want to make the 25th of each month the cutoff day, and the current formula I have is =EOMONTH([@[Date Returned]],0)-5 but for moths that are 31 days, it'll show that the date is the 26th. How can I add that to the formula?
Thanks
Hi!
To determine the 25th of the current month, use the DATE function.
=DATE(YEAR([@[Date Returned]]),MONTH([@[Date Returned]]),25)
Hello Sir Alexander,
Please how do i convert a whole number like "100" to get the number of days and months in that 100.
let say 75 = 2months|15days
100 = 3|months|10 days
I will be glad to get a solution through mail from you Sir.
Thanks
Hi!
If you think each month has only 30 days, try the rounding formula
=INT(A1/30)&"m "&MOD(A1,30)&"d"
Hello,
I need a help on the formula to automate the Collection of Fee in the Following Scenario:
Voucher is issued and Due Date is 10 the of Month
Now Depending upon the Collection Date i.e Current Date ( Today() )
a) If Collection Date is Between 1-9 of current month Then Collect the Due Fee
b) If Collection Date is Between 10-20 of current month Then Collect the Due Fee +100
c) If Collection Date is Between 21-30 of current month Then Collect the Due Fee +200
d)If Collection Date is 31 of current month or over into the subsequent months Then Collect the Due Fee +400
Hello!
Use the date functions to create the dates you need and use them as a conditions in a nested IF function.
=IF(A1<DATE(YEAR(TODAY()),MONTH(TODAY()),10),B1, IF(A1<DATE(YEAR(TODAY()),MONTH(TODAY()),21),B1+100, IF(A1<=DATE(YEAR(TODAY()),MONTH(TODAY()),30), B1+200,B1+400)))
I hope my advice will help you solve your task.
Thanks Alot Alexander, it worked.
We have the problem that the formula does not work with "mmm" but with "MMM", is this a version thing or something else? Hope someone can help me figure this out.
Best regards,
I am trying to have a date populate based on the data of an assessment, here are the scores:
0-9 none
10-18 Q 90 days
19-36 Q 60 days
37-54 Q 45 days
In first column will be the date completed, then the second column the score, based on that score, will be the date needed for the above information, what formula would be used for that? Notes: Q means every, if person scores 15 then another assessment will be completed in 90 days, if person scores 35, their assessment would be in 60 days.
Thank you.
Hello!
Use the example from the first paragraph of this article - Excel nested IF statement. This should solve your task.
I try to make my organisations automated attandance sheet in excel sheet where month start from month 26th to next month 25 th. As like 26th Dec 2021 to 25 th Jan 2022. But hear problem is when I give Feb month my sheet month count from 23 rd date. How is the solutation.
I try to make my organisations automated attandance sheet in excel sheet where month start from month 26th to next month 25 th. As like 26th Dec 2021 to 25 th Jan 2022. But hear problem is when I give month of March my sheet month count from 23 rd date. How is the solutation.
Hi!
To determine the month, use the MONTH function. Create a date using the DATE function.
=IF(MONTH(A1)=2,DATE(YEAR(A1),2,23),DATE(YEAR(A1),MONTH(A1),26))
hi, i have a model which have four different periodes in a table each in a cell like : " 01/01/2022 31/03/2022" | " 01/04/2022 30/06/2022" | " 01/07/2022
30/09/2022" | " 01/10/2022 31/12/2022 "
and i want to extract the period that refers to our actual date ( todays date 29/08/2022 so we will pick the 3 period ) and put it in a new tab.
please help me?
Hello!
Your periods are written as text. You cannot compare the current date with them. Sptit text into sepatate cells and use the DATEVALUE function to convert the text to a date.
thank you, it helped
Hello! I could use some help finishing this formula I am using. I need to formulate when 401(k) benefits start for employees. The employees are eligible after 1 year of service, but on the 1st of the following January or July, whichever is sooner. For example if someone starts on May 2nd, 2022, they will be eligible for benefits on 7/1/2023 (one year later, then the 1st of July since it is sooner than January). I have the following done, but I can;t figure out what to add to get it to calculate to that following January or July 1st.
=EOMONTH(B3+365,(DAY(B3+365) > 1)-1)+1
Thank you so much!!!
Hello!
Use the DATE function to get the dates you need. Compare these dates with nested IF functions.
=IF(DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)) > DATE(YEAR(A2)+1,7,1),DATE(YEAR(A2)+2,1,1), IF(DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)) > DATE(YEAR(A2)+1,1,1),DATE(YEAR(A2)+1,7,1),""))
I hope I answered your question. If something is still unclear, please feel free to ask.
Amazing. Thank you so much!!!
Amazing. You are beyond helpful!!
Hello, kindly assist on how I could get the last entry per month in a range of different dates and months
Hello!
To retrieve a record from a table, use the FILTER function.
If I understand your task correctly, try the following formula:
=FILTER(A2:D11,EOMONTH(DATE($G$1,$H$1,1),0)-C2:C11=MIN(IF(EOMONTH(DATE($G$1,$H$1,1),0) > = C2:C11,EOMONTH(DATE($G$1,$H$1,1),0)-C2:C11,99999)),"")
G1 - year, H1 - month. Column C - Dates.
Hello!
Could you please help with an issue I am having. I am using =TEXT(K4,"mmmm") in cell L4 to show the month of the date entered in cell K4, but I would like L4 to show as blank until there is input in K4.
I hope this explains clearly what I am trying to do.
Hello!
Use a condition with an IF function.
=IF(K4 < > "",TEXT(K4,"mmmm"),"")
This should solve your task.
Thank you, this works perfectly.
Hi Sir..If a cell in Excel contains 4.5 (Four and Half) number which represents number of months. How can i deduct 4.5 from today's date in excel
Hi!
I already answered you in this comment. Why are you asking the same question again?
Hi,
I wanted to know if it would be possible to have a cell generate the number of days left per calendar month automatically by today's date so that it can auto countdown/reset on the first rather than me having to manually -1 each day.
Hi!
I don't really understand what you want to do. But to determine the last day of the current month, you can use the EOMONTH function, as described above. The following tutorial should help: Calculate number of days between two dates in Excel. Hope you’ll find this information helpful.
I have a list of pay days in the month i.e 12th, 15th, 25th LWD
Is there a way in a column firstly to create the date for the month i.e. 12/07/2022 25/07/2022 and then doesn't matter if separate column amend as to whether it is a workday work out in a particular month if that is a workday
We already have a formula for the payment to go column - the last one in the example below, which is =WORKDAY([@[Pay date this month]],-2,Category[Holiday Date]) but want to create the date in the middle column without having to manually do from the singular date in the Pay date column
Pay date PAY DATE THIS MONTH Payment to go
25 25/07/2022 21/07/2022
Not sure if possible but your thoughts are welcome - Thank you
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Give an example of the source data and the expected result.
I have been trying to construct a formula that accomplishes the following.
Counts the number of entries before a given date (15th of every month)
If that number is greater than 8 then it produces $250
If it is less than 8 it produces 0.
I would like the formula to be usable in any given month since we have separate sheets for each month.
Hello!
To count the number of entries in a date range, please have a look at this guide: COUNTIF formulas for dates.
The formula might look like this:
=IF((COUNTIF(B2:B100, " > = 6/1/2022")-COUNTIF(B2:B100, " > 6/15/2022"))>8,250,"")
I hope it’ll be helpful.