Comments on: Using MONTH and EOMONTH functions in Excel - formula examples

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 page 9. Total comments: 493

  1. 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

  2. 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?

  3. 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

  4. 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

    1. 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")

  5. =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.

  6. 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.

  7. 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.

  8. 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?

  9. 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!

  10. 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.

  11. Is it somehow possible to convert dates in the format like this: Thursday 31 January to 31-01-2019?

  12. 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

  13. 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

    1. 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 ....

  14. 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

  15. 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

  16. 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?

    1. 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?

      1. 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?

        1. 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.

  17. Hi There,
    Just want to say thanks for this great help.

    Best regards,
    Numan

  18. 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).

  19. 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.

    1. 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,

  20. Hi Master,

    How to convert e.g; Jul-04-2018 to 04-07-18. Thank you.

    1. 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.

  21. 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!

  22. Project start date 11-May-2018. Project completion is 15 months from start date. How to calculate in DD-MMM-YYYY format.

    1. 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.

  23. 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!

  24. 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!

  25. 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

  26. How do I convert 10-17 to end of month 10/31/2017

  27. 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 !!!!!

  28. 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....

    1. 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.

  29. 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?

    1. 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.

  30. how to find out next month name from previous month name by using excel formula

  31. Starting Date Given suppose e.g 5 Feb 2014 Contract Period is 3 year.How to find the Contract Completion Date ?

  32. I have enter 22/02/2017 enter another cell 24 month howt to multiple in month in same date.... Ex 22/02/2019

  33. TB PESENT KO 1 MONTH ME RS.500 DENA HAI AND HIV PESENT TO 1 MONTHME 1000 DENA HAI SARE PESENT EK HI SHEET MA HAI AUR KESEKO 6 MONTH KA PEISE DENE HAI OR KESEKO 8 MONTH KE PAISE DENE HAI TO EXCEL KAISE FORMULA DENI CHAHIYE.

    PLZ SEND EXCEL FORMULA

  34. I have a date of say 20170501 in cell A1, and need B1 to show the end of the month of whatever month is in A1. So in this instance it would need to show 20170530.....if A1 was 20170330 it would need to show 20170331....and so on. Is this possible?

    1. Hello, James,

      enter the following into B1:
      EOMONTH(A1,0)

      Don't forget to change the format of B1 to Date.

      You can learn more about this function here.

      Hope it helps!

  35. I would like to know how many actual working days will be in a particular date range for budgeting purposes. For example if a contract starts on a given date in cell A2 and has an end date in cell B2, the number of actual working days are displayed in cell C2

  36. Hi

    I have last 7 months production data of different products.some of products have no outcomes for 2 or 3 months continuously. How can I identify the particular product from lakhs of products. Is there any formula available for it?

    Regards,
    Senthil

  37. Hi....,

    I have a two different dates, for example
    1. 1st march, 2015
    2. 15th march, 2015 in same month and just i want to know after completion of 1 year will be 1st march, 2016 and 15th march was moved to 1st April.

    here what will happend means i use this formula

    "=EOMONTH(F419,12)+MONTH(1)"

    it will be showed as 1st march 2016 for 2nd date also,

    can you please suggest me what is the exact formula for that.

    Regards,

    P. Bhanu Prasad

  38. I have a cell with date. I want to change the format of that cell after the last date of that month. Suppose the cell has value 3/22/2017. The cell formatting should change once the date reaches 4/1/2017. How can I do that?

  39. Hello, I used the formula you shared "=DATE(2017,MATCH($A$1,$N$1:$N$12,0)+COLUMNS($A$2:A2),1)" which worked great to fill in the series of months. Now I can get my sheet to automatically fill in the series if I select March or July. Is there a way to have it fill in only the number of months I need? For example. If I select January as my starting month and only need it to fill the series through July (6 months). Or 9 months, etc. How can that be done?

    Thanks

  40. Sale data is 1 to 31 days already have in row and then 120 shops in column.I want known What shop sale data no have 4 days series.

  41. Dears
    i have date of joining (21-09-2011), suppose 24 months contract, what will be my next vacation date, need formula in excel.

  42. Hello ma'am
    I want to know difference in month (not full month)between two date inclusive of both date.
    Pls help me
    E.g.5-1-2017 to 1-2-2017
    Ans.is 2 month

  43. date is like-02/03/2016,
    that is not 02-March-2016
    that is 03-Feb-2016.
    how to make this as dd/mm/yyyy

  44. HI
    i was trying for if command to change month / retain the month

  45. Hi, I'm wanting to find a formula which will highlight dates that are not in the current month. Thanks in advance.

  46. I'm using =datedif(A1,B1,"d") to calculate the days of the month but if the month has 31 days the result is 30 days. Is there any other formula to use to calculate the 31 days? Thanks

  47. I'm using =datedif(A1,B1,"d") formula to calculate the days of the month but when I put the start day 10/1 and the end 10/31 counts 30 days and I don't know how to make to count 31 days that is what I need. I'm working in a foster care agency and to pay the providers I need to calculate exactly the days that every month has. Please if there is another formula could you share with me.
    Thank so much.

    1. Use formula =datedif(A1,B1,"d"+1 to get the desired output.

  48. I want to be able to populate a word document with data from an excel spread sheet, but, not all the data. Each month for our newsletter we publish birthdays for the month. Is there a way that I can write a VBA or a formula that will each month take data just for that month with regards to birthdays and anniversaries and populate our word document. Basically I want it to select data for example for November and publish only that data in the document rather than the entire excel spreadsheet that has data for a full year.

  49. Hi!
    I got answers after several INDEX & MATCH equations as YYYY-MM as to view. But cell is not formated as date.
    Now I need to substract several month from above type answer and get the final result as YYYY-MM type.
    eg.
    Answer Months Final Answer
    1987-9 17
    2014-11 14
    2005-3 18
    1984-3 31

    regards

  50. I start with 30.0 days in Month#1 and want to subtract 2.5 days per following month. How can I get an automatic, based on the current month, "running" total of the days remaining month by month?
    Sep=Month#1=30.0 days
    Oct=Month#2=27.5 days
    Nov=Month#3=25.0 days and so on

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)