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!
470 comments
Hello,
I would really appreciate your help if you are able to please.
I have a column containing dates (dates of birth). I would like in the next column an if statement if the individuals birthday is within the next 7 days to show as true.
How would I go about that please?
Thank you in advance.
Hello!
The answer to your question can be found in this article: Using IF function with dates. To determine the birthday, use the DATE function.
If I got you right, the formula below will help you with your task:
=IF(AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))-TODAY() < 7,DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)) > TODAY()),TRUE,"")
I hope you can help me with a formula that will return a cell address based on the month number. I may have the first part that looks like this: =MONTH(A1:W1)=MONTH(D40). The D40 holds the number of the current month. The formula will always return a True/ How would I use this to display the contents of a cell for that particular month. The cell for all months appear on the same row and directly under the date column, eg. the cell that I want to display for June (K1) is in K20, for July(M1) cell to be displayed is in M20, etc.. Any help you can provide would be greatly appreciated. Thanks.
Hello!
If I understand the problem correctly, you will find this article useful: Excel INDEX MATCH MATCH and other formulas for two-way lookup.
Try the following formula:
=INDEX(A1:W20,20,MATCH(D40,MONTH(A1:W1),0))
Many thanks to you and your team. Your formula combined with the need to enclose it with a CTRL-SHIFT-ENTER was just what I needed. I have had some SQL coding experience but could not have come up with this solution on my own. I took some time to investigate the #N/A error without success. It was only after having a look at your resource article that gave me the final piece. All the best to the team at Ablebits.
please help me
if today 2nd day of current month is monday and 1st date of current month is Sunday then return last month end date in excel.
Example if today 02-05-2022 is Monday then return last month end date (30-04-2022)
please help me
Hi!
To determine your conditions, use the DAY function and WEEKDAY function. Try this formula:
=IF(AND(DAY(A1)=2,WEEKDAY(A1,2)=1),EOMONTH(A1,-1),A1)
I hope my advice will help you solve your task.
Thank you Boss..
i need one more help.
if today 2nd day of current month is Monday and 1st date of current month is Sunday then return last month end date or if 1st date of current month is not Sunday then return Yesterday Date.
it's is possible ??
Hi!
Subtract one day from the date you got in the formula.
Hi,
I'd really appreciate your help.
I have a column of dates eg.01/02/2021. I'd like to be able to have a formula so that in the next column it shows just a month eg. December.
I'd like this to be worked out like this: if the day falls in the first half of the month (1-15) the month stays the same in the second column e.g. column 1 01/02/2021 , column 2 reads February. If the day falls into the second half of the month 15-31 the second column will move to the next month. eg. 16/02/2021 second column would read March. Is that possible?
Thank you
Hello!
Specify the day of the month using the DAY function. If it is the second half of the month, use the EOMONTH function to set the date of the next month. You can get the month name with the TEXT function.
The formula below will do the trick for you:
=IF(DAY(A1)<15,TEXT(A1,"MMMM"), TEXT(EOMONTH(A1,1),"MMMM"))
This should solve your task.
Great piece of code there, Alexander. Thank you for that!
Hello
I need your help here. I want in a sheet, when I write at A1 the name of the month, starting from A2 to show me the dates. So if at A1 I write january, At A2 to write me 01/01/2022, at A3 02/01/2022 till the end of month. Can you help me in this please?
Thank you
Hello!
Use the VLOOKUP function to find the month number. Get the date using the DATE function.
The formula below will do the trick for you:
=DATE(2022,VLOOKUP(LEFT($A$1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0),ROW()-1)
Try to enter the following formula in cell A2 and then copy it down along the column.
Set the date format in the cell to whatever you need.
hi,
can u help on this
for example in one cell I wrote April, May
and if todays month is march I want to get a notify like active in cell next to it
Hi!
I can suggest you to do conditional cell formatting based on date.
thanks
Hi,
I need help in creating a formula giving me the sum of dates (particularly dates in last month) appeared in the column.
I have list of dates in Column G and need the answer in the column M.
I am sorry if you have answered the similar question earlier.
Many thanks for your help in advance!
Hi!
Sorry, I do not fully understand the task. Please explain what you mean by "sum of dates ".
Perhaps you will find this article useful: Excel SUMIFS date range formula - sum if between two dates.
If I'm not guessing, please explain the problem in more detail.
I have list of data
Transactions as per client with the transaction date. one client has done several transactions each month. I need the sum of transaction client has done in previous month say (April-22)
Column B(Clients), Column G (transaction date). If client client A has done 9 transactions total and previous month (April) he has done 3 transactions
In column M, I need the result 3.
To add the dates are different in previous month
Hi!
You need to count transactions by client name and by date range. The following tutorial should help: COUNTIFS and COUNTIF with multiple criteria for dates.
Here is an example formula for you:
=COUNTIFS(G2:G9, ">=4/1/2022", G2:G9, "<=4/30/2022", B2:B9,"A")
I hope my advice will help you solve your task.
Thank you for your reply.
I tried the above formula but no luck. Can you tell me what is "A" referred to.
Hi!
You wrote: "If client A has done 9 transactions"
"A" - client's name
You can learn more about COUNTIFS syntax in Excel in this article on our blog.
Hello,
Is there a way to get a box to fill to highlight if today is the date in the box (e.g. for birthdays)? I can't seem to figure it out.
For example, in column L I have their date of birth, in the format DD/MM/YYYY, and I'm hoping to have this flash when today is their birthday? Or if that's not possible, have the column next to it, M, flash when again it is todays date in the column L
Hello!
The answer to your question can be found in this article: Conditionally format dates in Excel based on the current date.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Hi, I'm struggling to create a formula and would really appreciate your help.
So, I want to add a formula to O5 to...
Count the number of sessions (column D5: D100) held by a specific coach (column C5:C100) within each month (date column B5:B100, then column O4 which states e.g. April 2022 and then each month prior where I can drag the formula along)
I hope this makes sense.
Thank you in advance!
Hello!
If I understand your task correctly, try the following formula:
=SUMIFS(D5:D100,C5:C100,"John",B5:B100," > ="&DATE(2022,4,1),B5:B100," < ="&DATE(2022,5,1))
You can learn more about SUMIFS function in Excel in this article on our blog.
That's amazing! Thank you so much!
Hi I have an excel sheet with two columns:
Suppose this is column A
A
4 Y, 5 M, 2 D
and this is B
3 Y, 4 M, 1 D
I want to subtract these two columns. I want a third column which shows the subtraction of A and B. The data is 3 years 4 months 1 days (in this format) so I want the answer to be in the same format in a single cell. Can anyone help me with the solution? what formula to use?
Hi!
Your values are text. You cannot subtract them.
how do i change it?
Hello, so I have quite the predicament. I have 4 columns which have the following:
Column A: 3/14/2022 (A specific date)
Column B: 3/11/2022 (Start of 13 day period)
Column C: 3/24/2022 (End of 13 day period)
Column D: Needs to return the current Start Date (3/11/2022) and auto-update to (3/25/2022) when the time comes. So basically, I need D1 to show the current start date from column B and have it automatically update to the next start period once reached which is the 25th, and then again after the next 13 period.
I also forgot to mention that Column 1 contains the formula =TODAY()-WEEKDAY(TODAY(),16) which gives me the first date I need to work with (3/25/2022) and the cell to the right of that has CELL+13, so I guess the cell above 3/25/2022 would have -14
Hello!
If I understand you correctly, the following formula with nested IF functions will help you with your task:
=IF(AND(A1 > B1,A1 < C1),B1,IF(A1 > = C1,C1+1,""))
I hope it’ll be helpful.
"Would like to apply the column with the condition.
In sheet1, the user will input data.
Shipping date logic as below:
If Date (sheet1 column A) is not the current month Period (reference Period) can leave blank.
If Date (sheet1 column A) is the current month Period (reference Period) force to input something. Example line 9-10.
The reference table will be changed yearly. "
Hello!
To compare the month of the date in column A with the current month, you can use a condition like this:
MONTH(A1)=MONTH(TODAY())
Good day,
Please assist with the following:
I am using the following formula to calculate days between dates:
=IF(OR(E22="",F22=""),"",IF(H22="N",0,IF(OR(E22>EOMONTH(E22,-1)+1,F22<EOMONTH(F22,0)),DATEDIF(E22,F22,"MD")+1,0)))
Which results in e.g 01 February 2022 until 28 February 2022 to calculate 0 days (which is what I want)
However, I am looking for that 28 days to count as 1 month and move over to the "Month" column as 1 month - Please assist with a formula.
Your assistance will be appreciated.
Kind regards,
R.
Hello!
The date 28 February 2022 in Excel actually means 28 February 2022 00:00:00. That is, from February 1 to February 28, there are actually 27 days. If you want the DATEDIF formula to include February 28 as well, add 1 to that date. That is, use F22+1 instead of F22 in the formula.
I hope my advice will help you solve your task.
Please see example below to possibly assist:
From 01 January 2022 until 31 January 2023 = my calculation gives 1 Year; 0 months; 0 days (each in its own column with its own formula).
However, from 01 January 2022 until 30 January 2022 = my calculation gives 1 Year; 0 months; 30 days (each in its own column with its own formula).
Taking the above in account, looking for a formula to take over that days that makes a full month over to the month column to make 1 month (currently my calculation shows 0 months and 0 days for the days that makes a full month i.e. 01 April until 30 April etc.)
It is noted that a +1 can be added but seeing as the excel sheet needs to be locked it will not work as the following would happen in the case of example, from 01 January 2022 until 05 February 2023 = the calculation will be 0 Years, 2 Months and 5 Days (which is incorrect as the +1 in the months calculation results in that extra month.
Been struggling with this for some time, so a formula would be appreciated.
Kind regards,
R.
Hi!
If you carefully read my comment, you will see that I advised you to add 1 to the last date. Not for a month. In the article that I advised you, pay attention to the paragraph How to get date difference in days, months and years.
=IF(DATEDIF(E22,F22+1,"y")=0,"",DATEDIF(E22,F22+1,"y")&" years ")&IF(DATEDIF(E22,F22+1,"ym")=0,"",DATEDIF(E22,F22+1,"ym")&" months ")&IF(DATEDIF(E22,F22+1,"md")=0,"",DATEDIF(A2,F22+1,"md")&" days")
If necessary, split this formula into 3 cells.
HI
i want to know how to end my month dated when i am using =today()
Hi!
I am not sure I fully understand what you mean.
HELLO, I want to know where these dates will fall under what month . 1/25/2021-2/16/2021, but I need to change the starting date of the month so I consider it as month 1 with these date 1/24/2021-2/24/2021 and month 2 for 2/25/2021-3/24/2021 . So I need to know what formula for this between these date range 1/25/2021-2/16/2021
Hi!
Try subtracting 24 days from the date -
=MONTH(A1-24)
sir good evening.
in number 0-6-0
come date format like 0-6-0
Very good info ! Thanks !!!
hi, I want to format dates based on how many months are left before they are due for renewal.
for eg. column F5 contains the date 19/02/2021, column E5 tells me that there are 12 months before this is due for renewal. How do I write the formulas so that
a) can mark when its 3 months less than value in column E
b) when its past the value in column E
Hello!
You can find the examples and detailed instructions here: Excel conditional formatting for dates & time.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
I need to calculate rent month-wise based on start and end date, please help
Rent per month - 3000
Start date - 14-Jan-2022
End Date - 22-Aug-22
How to calculate month-wise rent between start and dates
Hello!
To calculate the rent for January, use the formula
=(EOMONTH(A2,0)-A2)/31*3000
in excel if i want to pay salary per month is 25000 and date 15-02-2021 to 8-11-2021 how i can calculate automatically in month wise and also 14 days of feb and 8 days nov amount calculate by month wise per day rate
Hi!
Your question is not entirely clear, please specify.
Hello,
I am trying to create a purchase order ID from a mix of data, which should include the purchase date in four figures only. For that I need to convert the day and month to their number forms with no space in it. For example, an order was place on November 9 and I want my Purchase Order ID to have this info in it, and in order to do it the month and day should be like 1109. All the date in word format should be automatically converted to number format as mentioned above. Could you please help?
Thanks.
Hi!
Specify exactly how the date you want to convert to a number is written.
Perhaps this article will be helpful: How to convert text to date and number to date