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. Continue reading
Comments on: Using MONTH and EOMONTH functions in Excel - formula examples
by Svetlana Cheusheva, updated on
Comments page 8. Total comments: 495
I need to take a date from this format 3/31/2020 to the "31st day of March 2020". I have NO problem on the March 2020 part but HOW in the world can I get the st part of 31st ??? Also need the abbreviations for all days 1, 2, 3, ..... 29, 30, and 31. Like 1st. 2nd, 3rd, and so on. The Only workaround I can think of is to create a table or chart with all the possible numbers with abbreviations and then use vlookup or the new xlookup.
Thanks in advance for any help or insight !!!
Hello Carl!
If I understand your task correctly, the following formula convert to ordinal date format:
=DAY(A1)&IF(OR(DAY(A1) = {1,2,3,21,22,23,31}),CHOOSE (1*RIGHT(DAY(A1),1),"st ","nd ","rd "),"th")&TEXT(A1,"mmmm, yyyy")
or
=DAY(A1)&IF(OR(DAY(A1) = {1,2,3,21,22,23,31}),CHOOSE (1*RIGHT(DAY(A1),1),"st day of ","nd day of ","rd day of "),"th day of ")&TEXT(A1,"mmmm, yyyy")
I hope it’ll be helpful.
Hello Michael!
Please use the following formula
=DATE(YEAR(A1)+2, MONTH(A1)+9, DAY(A1)+0) or
=DATE(YEAR(TODAY())+2, MONTH(TODAY())+9, DAY(TODAY())+0)
We have a ready-made solution for your task. I'd recommend you to have a look at our Ablebits Tools - Date&Time Wizard.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Hello,
I have calculated the number of year and months in excel. Now I am trying to get Excel to tell me what month and year that will be from now. For example, my car note will be paid off 2 years and 9 months from now, March 29, 2020. How can I get excel to tell me that 2 years and 9 months from now will be in December 2022? I thank you for your help with respect to resolving this issue in advance.
I need a formal that if a month has 31 days it will displace from cell D5 and if then displace from cell D4. the month is in A1. Can you help me, please?
Hello, Brenda!
If I understand your task correctly, the following formula should work for you:
=IF(DAY (EOMONTH (DATE(2020,A1,1),0)) = 31, D5, D4)
Thanks a lot for your information. I have an issue to convert the following text date to a real date type.
Jan 02, 2018
Would you help on how I can convert it to date type? I've tried "=date(RIGHT(A2,4) LEFT(A2,3) MID(A2,5,2))", but it doesn't work.
Hello? Mark!
Please use the following formula
=DATEVALUE(A2)
=DATA.VALue(replace(replace(a2;".";"/");" de ";""))
01/01/2020 AMUH
02/01 DLD
03/01 AMUH
04/01 ARASH
05/01 DAFZA
02/02/2020 AMUH
04/02/2020 UTPM
01/03/2020 UPTM
07/03/2020 AMUH
04/04/2020 AMUH
HOW CAN WE CALCULATE MONT WISE
KINDLY SHOW THE CALCULATION
I have few date range with different months and year. I want to hight only date which are from January to June regardless year. Can you please help me for this?
Example.
04-12-2006
05-06-2007
20-08-2008
05-01-2009
09-11-2007
25-06-2008
08-01-2009
24-06-2008
01-02-2011
17-11-2011
24-09-2011
05-09-2012
30-09-2011
24-09-2011
04-07-2019
04-01-2009
31-12-2013
01-12-2013
01-12-2013
01-12-2013
22-01-2014
25-06-2008
01-12-2013
17-02-2012
01-12-2013
12-03-2016
12-03-2016
12-03-2016
12-03-2016
12-03-2016
12-12-2017
07-05-2019
10-07-2019
10-06-2019
05-07-2019
05-07-2019
29-07-2019
28-09-2019
from above dates I want to hightligh only dates from January to June regardless year.
Hi,
Struggled to understand this, =TEXT(A2*28, "mmmm"). Why multiply by 28?
Hi Sam,
Thank you for a very good question!
In its internal system, Excel considers the number 1 as Day 1 in January 1900 (when used together with month format codes such as "mmm" and "mmmm"). By multiplying the numbers 1, 2, 3 etc. by 28, you are getting Days 28, 56, 84, etc. which are in January, February, March, etc. Multiplying by 29 will also work.
I've also added this explanation to the tutorial for other readers.
I am facing a problem that I am using to display a stacked bar chart with "Time" as the timeline. However, I don't know how to make the x-axis (timeline) to display 1st day of each month. Any idea?
hi
i have one question, i have the calendar in excel and i am entering holidays next to the particular date. when i change the month, how i can clear the my holiday entries automatically.
thanks
Problem solved:
I used this-
=AND(MONTH($A1)=MONTH(TODAY()),YEAR($A1)=YEAR(TODAY()))
Problem solved:
I used this-
=AND(MONTH($A1)=MONTH(TODAY()),YEAR($A1)=YEAR(TODAY()))
Now I want the rest of the date should be highlighted in yellow colour. I mean if the month and the year is less than or more than today's month and the year it should be highlighted in yellow colour.
I have different dates in column A1 (1-Oct-19, 15-Oct-19,29-Oct-19, 12-Nov-19, 26-Nov-19, 10-Jan-21 and so on). I want to highlight only the current month in green and the rest of the month in the red. I mean before and after Jan in the red and Jan (Current Month) in green.
Example 1. Highlight dates within the current month
=MONTH($A2)=MONTH(TODAY())
Through this, if year changes it is highlighting the same month also. How can I highlight the date within the current month and year?
10-Jan-20, 15-Jan-21
It will highlight both cells. When the year is 2020, it should not highlight the year 2021.
I have dates in a row (dates span across months) and certain values in the cells below these dates (e.g. work shifts such as "M" for morning). I need to count number of occurrences of a shift per month. How to I achieve this ?
I tried using MONTH() and COUNTIF()/COUNTIFS() together but failed.
if i have a sum or number like 28, 30 or 31 how i count these no. as 1 month
i try formula =DATEDIF(0,F26,"ym") but it show 0 and formula =DATEDIF(0,F26,"md") it show same no. as 28, 30 & 31.
i want it show as 1 month if any solution or formula pls.
how calculate the sum 31 as a month or how to show 31 as (1 month or 1) in excel
i try =DATEDIF(0,F25,"ym") (where F25 is a no. or sum =31) but is show only 31 as result instead of 1.
Hi, may I know the formula in getting the specific month for a transaction that has been closed? The Status is Open/Closed then I want to get the month of the date when the status has been closed. Hope you can help. Thanks in advanced
Please tell me how to convert the the date format from 07/25/2019 to 25/07/2019
Assume your data in A2 Cell
DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2,1)-1),MID(A2,FIND("/",A2,1)+1,2))
hi
i have need excel formula for below query..
a6b8c9fg43gg44rrfd43dfg55
how to count alphabet and number please suggest
Anybody know? Why the text formula must to multiply with 28 to get the month name.
Example
1(A2) then = Text(a2*28;"mmm")
That's blow my mind.
Please answer it admin
Hi, i have a difficulty and trying to find a formula. Below is the scenario.
Start date: 03 Mar 2019
End date: 21 Jun 2019.
I want to count that the
1. downdays for month of Mar'19 is (end of month Mar'19 subtract 03 Mar 2019)
2. downdays for month of Apr'19 is no of days in Apr'19
3. downdays for month of May'19 is no of days in May'19
4. downdays for month of Jun'19 is 01Jun to End date
I only know that if i subtract End date and Start date is XX days, but i want the segregation into respective month.
Hi ,
I want one formula ,i need count of current date of current month, without using range cell
Thank you
Hi,
I have this text in a cell 2019-01-15T15:38:05
And I want to substract the month either as number or name (March, April, etc)
How can I do it?
Thank you
Hi , I am trying to extract month from date in DDMMMYYYY format. I am using "=MONTH(DATEVALUE(B1)) ".
Aththough this was working for dates like 30APR2019 but it's not working for 01MAY2019.
Cannot figure out why it's behaving different for different month as all cell formats are the same.'
Can anyone help out on this?
Hi,
i want to add Provident Fund Amount Automatically when new Month Start .How can it will be added automatically in Specific Row Using Excel 2013
Thanks
When I am using TEXT formula for the date 01-10-2019 and i use =Text(A1,"dd/mm/yyyy") in b2 i will found 10/01/2019 how can i fix it
Hi Jayesh,
Just use the desired format code in the format_text argument. For example, to display the date as 01-10-2019 (where 01 is the month and 10 is the day), use this format code "mm-dd-yyyy":
=TEXT(A1,"mm-dd-yyyy")
=MEDIAN(DATE2,DATE1)
is a good formula for determining on what date an event that straddles multiple months falls. For example I am doing a monthly analysis but some events straddle multiple months (most only straddle two when they do so this works). I take the median which tells me on what date an event straddling two months falls. Then I format the column with the customized mmm-yyyy style. This is a good method for determining the midpoint of an event that falls mainly in one or two months.
Hello all,
While =Text(A2,"mmmm") function gets you the month of the date, it is displaying 'January' for blank cells. How can be avoid this such that the cell remains blank and should not display 'January'.
Thank you.
Hi, I wanted to multiply $9 per month including any partial month.
2/5/19 and 3/15/19 * $9 - my answer should equal $18
Looking for a simple formula.
I'm wanting to get a text month name for a certain date range each month from a date cell. If the date is between the 27th of one month to the 26th of the next month I want it to state only a month name. Example: If the date is between 12/27/2018 to 01/26/2019, I want the cell to say "JAN". If the date is between 01/27/2019 to 02/26/2019, I want the cell to say "FEB". Is there a formula for this?
Hi I would like to check if its possible for the extracted month to be automatically updated?
I have a column for month with the formula: TEXT(C17,"mmm") for example.
If i happen to change the date in cell C17, the month cell does not automatically update to the new month input.
Any help would be greatly appreciated!
Thanks!
What formula can I use to display the date after six month,
example : 01-01-2019 current date , after six month 30-06-2019
explain in formula please.
Is it somehow possible to convert dates in the format like this: Thursday 31 January to 31-01-2019?
What the VBA code to display the previous month and current year?
I'm using the below code to display the current month and year.
With Range("H2,J2,L2,N2,P2")
.Value = Date
.NumberFormat = "mmmm yyyy"
End With
I need formula for Increment of salary
Conditions are as follows if joining of employee within jane to june then increment month should be Next jan if within july to december it should be Next july
Hello,
I Need a formula for every month days of number ; if i text a any name of month and then automatically changed number of days ...
as like I am writing January and then automatically changed number 31 & February changed to 28 etc ....
Hi,
Can you help me in below query.
I have 2 date like 15 Aug 17 to 20th September 18. From this two details ,I have to extract the month and days in each months.
Pls explain me how I can extract the details
Hey Svetlana,
That's awesome! Thanks so much. I actually figured it out over the weekend using:
=SUMPRODUCT(((MONTH(1&A2:A15)=E2))*($C$2:$C$15))
The little '1&' before the range in the MONTH function nearly killed me! I really appreciate your help with this and find great value in your articles.
Thanks, again,
Jeremy
Hi,
Thanks for the great info. In the section How to sum data by month in Excel, your example with the sumproduct formula returns a #value error for me when I use your data. Is there a mistake in that formula?
Hi Jeremy ,
I have just retested the SUMPRODUCT formula, and it works fine for me. It's difficult to say what the problem may be without seeing the actual worksheet. Try to use it on a new sample sheet with just a few data entries. Does it also return an error?
Thanks so much for your reply. I'm so sorry - I had a typo on one of the dates. It works now.
But, what I am trying to do on my spreadsheet is to get a similar calculation where the dates are simply 'January, February...' That gives me the #value error again. Would you have a solution for that, please?
Jeremy,
If the names of the months are entered as text, then you can convert them to numbers by using this formula. And then, use a simple SUMIF formula to add up the amounts for the desired month.
The same result can be achieved with this formula:
=SUMPRODUCT((MONTH(DATEVALUE($A$2:$A$15&"1"))=E2)*($C$2:$C$15))
Where A2:A15 are the month names, C2:C15 are the numbers to sum and E2 is the number of the target month.
Hi There,
Just want to say thanks for this great help.
Best regards,
Numan
What formula can I use to display the date, IF the month has 31 days? If not, NO entry should appear. The month would be drawn from a formatted cell with a date of the 1st or 16th (depending on the pay period start) of each month...
So, If my original date cell shows 9/16/2018; then my resultant date cell would have no entry. However, IF my original date cell was 10/16/2018; the resultant cell would return 10/31/2018 (a valid date).
Goodday.
i have a question. I have set of dates in a year (cell D1 to D20). I need to count how many days in specific month. Right now, I have use formula COUNT(IF(MONTH(D15:D17)=1,1)), to count how many dates occur in January and i use CTRL+SHIFT+ENTER.
My problem is, this formula works for february to december, but not for january. If i key in this formula, it will count all the cells eventho its empty unless i key in dates not in january. For example, D1 to D20 is about 20 cells, if i key in this formula, it will give me 20. If all the 15 of the cells dated january, and another 5 cells is empty, it will count as 20. And if the 15 cells dated january, and another 5 cells is dated february or other months, it will count as 15.
please help me to solve this problem.
Elly:
When I want to count occurrences of a date or how many times a date between two dates occur in a list I use this formula:
=COUNTIFS($O$11:$O$22,">=9/1/2018",$O$11:$O$22,"<=9/30/2018")
Then I label an adjacent cell with the appropriate date.
You'll need to enter the dates you want, but you would have to do that using yours, too,
Hi Master,
How to convert e.g; Jul-04-2018 to 04-07-18. Thank you.
Dan:
Try to right click on the cell containing the date and select Format Cells then from the list click Date then select the date format you want to use.
Hi Guys,
I'm looking for a formula who could accomplish the following;
I need to have one cell show "firsthalf" or "secondhalf" month depending on the date values on other two cells; EG first cell shows date 07/01/18, second cell shows 07/15/18 I want a 3rd cell to return "firsthalf" text.
Let me know if you can come up with any suggestions
Thanks!
Project start date 11-May-2018. Project completion is 15 months from start date. How to calculate in DD-MMM-YYYY format.
Sumit:
You can use EDATE to calculate dates that fall on the same day of the month as the date you are interested in.
For example, in your case where 11-May-2018 is in A1 it would look like this: =EDATE(A1,15) returns 11-Aug-2019.
EDATE is useful for loans or payments of various types that mature or are due on a specific date.
If you want more than the month you can use:
=DATE(YEAR(A1),MONTH(A1)+15,DAY(A1)) and then add a date in the past or future as this formula shows with +15 in the month spot. Past dates would require a - sign.
Remember to format the cells in the date format you are comfortable with. They have to be a date, not text. Excel has a built-in date that formats the cell to display dates in the way you want. Right click on the cell, choose Format Cells then select the Date option form the list and you'll see all the various ways Excel can display your date. If that doesn't work go to the Custom option in the Format Cells list and you'll see more options to display numbers, dates and times.
Hello,
=TEXT(A1,"mmmm") returns the correct answer (the Month of the year) unless the cell in Column A is blank, then it returns December. What do I need to add to the formula so if the cell is blank the formula returns as blank?
Thank you!
I'd like to calculate how many holiday days are subtracted from weekdays every month, where I have a table with the LEGAL HOLIDAYS with column A as description of holiday and column B as date of holiday. in the next table I have the calendar month start date in column A, month end date in column b, workdays.intl in column c to calculate workdays with special weekends. In column d I need the formula to calculate the number of holidays to deduct in each of the calendar months based on the legal holidays table. can you please help?
Thanks!
I'm trying to create a revenue water fall with Start date and end date and Contract value.. I have created a formula but somehow its giving me the revenue after the end date as well.. below is the example.. can some one help me how to stop the revenue
Start Date End Date Value
23-May-17 22-May-18 30563.80785
May-17 Jun-17
754 2,543
=IF(TEXT($BB3,"MMMYY")=TEXT(CH$2,"MMMYY"),(($BE3/365)*((EOMONTH($BB3,0)-$BB3)+1)),IF(TEXT($BC3,"MMMYY")=TEXT(CH$2,"MMMYY"),($BE3/365)*DAY($BC3),($BE3-(($BE3/365)*((EOMONTH($BB3,0)-$BB3)+1))-($BE3/365)*DAY($BC3))/11))
after end of 22nd May 2018 also I'm able to see revenue being populated can someone help to built the formula to stop that revenue
How do I convert 10-17 to end of month 10/31/2017
Hello everyone !!!!!!
I am from nepal. In nepali date month of February consists above 28 days so if i want to write the date above 28 its date format will be yyyy-mm-dd instead of mm/dd/yyyy . how to make this format as mm/dd/yyyy.
Thanks !!!!!
I am working on a running "if" formula that is currently set up for 2017; however, with 2018 around the corner, i need to change this. Is there a way to pull the formula without a year? for example
=IF(I4Z5,"0",IF(I4>=Z4,(I5*0.5),IF(I4<=Z5,(I5*0.5)))))
I4 is the due date, Z3 is 3/31/17, Z4 is 4/1/17 and Z5 is 8/1/17
Is there a way to keep I4 with the year (i.e. 5/1/18), but use the Z* dates without a year?
Hope this makes sense....
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
I do have a column X "Due Month" and another "Task completed" .I want to do a vlookup whereby if the referenced cell in task completed column is "yes" then then vlookup should increment the month +1. Is that possible?
Hello, Fahim Idha,
Please try to add a helper column to your table and enter the following formula into it to increase a month by 1:
=IF(Y1="yes",X1+1,X1)
Then just copy the data from the helper column and use the Paste Special -> Values option to replace the values in Column X.
Hope this will help you with your task.