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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. i am trying to find each and every truck come in one months, so i fine each truck how many time comes in one mounts, how to find. what is the formula

  32. Hi

    I am trying to find a formula in excel that returns a value for the month number based on a financial year.

    For example 31 aug 16 returns a value of 2 rather than month 8

    regards

  33. Hi I have this formuls in F3 which works great
    =SUMPRODUCT((TEXT($I:$I,"mm-yyyy")="01-2016")*1)
    But I would like to change ="01-2016") to = the date value in cell E3 but I am not sure how I tried highlighting it but it returned #Name
    I would like to copy it down so the E3 changes to E4 etc

  34. HOW CAN I MAKE A LETTER CHANGE LIKE A,B,C CHANGE EVERY MONTH

  35. I am preparing a church membership database in excel. All members are giving church support fund monthly based on their income.Many of them give their support fund 3 4 months advance.

    I have a column as per details appended below

    MONTHLY FUND Date PAID AMOUNT PAID TILL

    250 01 09-2016 1000 January 2016 (In this column the month should automatically displayed according to the monthly fund entered for each member. Which formula I can use.

  36. Hi, trying to find a formula for travel tracking. need to show how many people turn their papers in timely vs late/rush travel papers...

    date of travel forms received 15 days before beginning date of in-state travel, and 45 days before out of state travel.

    column C- date of travel forms received
    column H- dates of travel 09/07/16 or 09/23/16-09/25/16 could also edit worksheet to have two columns(begin travel date and end travel date)

    Thank you,
    Sarah

  37. This was a great read and helped me with some answers i needed :).
    I am trying to find a formula for my work project..
    If I6 has a completed date of a project.eg 13/07/2015
    I want a highlighted cell in three years time(like a Gantt chart?).
    If each column k5 =2016 L5=2017 M5=2018 N5=2019

    =DATE(YEAR(I6)+3,MONTH(I6)+0,DAY(I6)+0)

    Thanks for the Help

  38. That's a very powerful explanation. Quick question on converting Month abbreviated name to Full name.
    I have downloaded bank statement that has date columns showing as "Jan 4, 2016". With Text to Column, I am able to separate Jan, 4 and 2016. Now if I would like to combine the fields into proper Date field, I can't do as the name of the month is Jan (in General format) and not January. so the Date formula is not working. Is there a way to get to January from Jan (in general format) and February to Feb? Unless there's another way that can help me save some time?
    Thanks you for your suggestions.

  39. I am looking to create a formula to pick up if something falls between two months but the year I am looking at is not specified.

    Eg If 01-Jan (any year) falls between 01/10/15-01/02/16

    Any help appreciated!

  40. I give one cell=name and other cell give month-year,how to use vlookup reference name&month-year

  41. if i enter a name of month in any cell, i want the total number of days in another cell

    if i enter august in any cell , the result will be 31
    thanks

  42. Hi
    I am doing a cashflow sheet. It is broken down into months. I have an estimate balance and then a real time balance that is updated daily. Is there a way to have a formula depending on the date will show estimate figure if it is still in that month, but once the month expires the real time balance figure is used?
    So two different sums for one cell depending on the date?
    Thank you.

  43. how can i get the formula for a birth date 10/29/2011 in months to show 55 months?

    1. you can't since its 57 months.

      try this. A1 represents the cell containing the birthday

      =(TODAY()-A1)/365*12

  44. This article is amazing and so is this website in general. LOADS of information published for everyone to read which is exactly the type of forum I'm looking for.

    I'm trying to use formula =DATE(YEAR(TODAY()), MONTH(TODAY()), 22)

    I am trying to utilize this formula to show a date for today's year and month for that specific end number, in this instance 22, which by today's date it would state 7/22/2016. What I really need is it to state to next date that falls under these standards. For instance. I want it to now state 8/22/2016 because 7/22/2016 has already passed. I don't know how to alter the month part to show the next month if today's month doesn't apply anymore because the month's date has already passed.

    Let me know what you come up with. I'd love to hear some feedback. I'm stuck between a rock and a hard place right now.

    1. Hi Amanda,

      You can use the IF function to check if today's day is greater than 22, and if it is, add 1 to today's month, like this:

      =IF(DAY(TODAY())>22, DATE(YEAR(TODAY()), MONTH(TODAY())+1, 22), DATE(YEAR(TODAY()), MONTH(TODAY()), 22))

  45. Hi Svetlana,

    I have an issue with my excel sheet in displaying dates incorrectly.

    The problem is when i collect a data for whole month which has several dates in it, it automatically converts the dates while in filter starting from 1st to 12th dates as months and remaining dates normally. Can you please help me on how to change/modify it.

    Example:

    These are considered as dates when using sort/filter.
    06-13-2016 22:31:05
    06-13-2016 22:24:03
    06-13-2016 22:11:07
    06-13-2016 14:33:19
    06-13-2016 14:05:25
    06-13-2016 09:05:43
    06-13-2016 08:20:03
    06-13-2016 08:00:05
    06-13-2016 03:09:29

    These are considered as months when i select sort/filter
    06-12-2016 23:24
    06-12-2016 18:11
    06-12-2016 17:03
    06-12-2016 16:00
    06-12-2016 12:47
    06-12-2016 12:07
    06-11-2016 23:56
    06-11-2016 09:45
    06-11-2016 02:29
    06-11-2016 01:34
    06-10-2016 19:12

    I have tried to clear the cache, delete files on Registry, re-installation of office and repair and still there is no change.

    Request your help.

    Looking forward to hear from you,

    Regards,
    Bhanu M

  46. how can i calculate a certain date in next month from any date of current date.
    Let some dates : 02.06.2016
    15.06.2016
    28.06.2016
    How can i get a date (Suppose the date's 06.07.2016) in all cases using a uniform formula in excel.

  47. there are two sheets in excel one contains data and other sheet has function based on date. i want to get the date depends on date there are many duplicate dates in data sheet it should consider all the data which contains the date in a cell in sheet to date in a cell. when i change the date the reflecting also must change.

  48. What's the formula to return a date into a particular period in the month for eg if the date is >= 15th day of the month, it returns 15th day of the same month .. If the date is <15th, it returns 1st day of the month .. Thanks

  49. Hi all,
    I'm trying to extract a month and day from a date (dd/mm/yy) for stats and have been using the =TEXT(C1,"dddd") and =TEXT(C1,"mmmm")formulas, which works fine. My problem is that when there is no date in column C it will auto fill the month with January and the days with Saturday, which gives false values through for stats. Please help

  50. I am very new to excel, and to using formulas, this is what I need, and I would think it's fairly simple for the trained.
    1. I have start and end dates. 4/1/2016 - 4/1/2021
    2. My FY begins 10/1/20XX and ends 9/30/20XX
    3. I need to fill a table that calculates the number of months per FY for the project.
    FY16 - 6, FY17 - 12, FY18 - 12, FY19- 12 fY20 - 12, FY21 -6.

    Any help is greatly appreciated

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 :)