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

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

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

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

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

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

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

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

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

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

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

  11. Hi!!

    can you please tell me how to write 5 years 11 months to 5.11 or 5-11 or by using any separator??

  12. I have a spread sheet filled with training dates for multiple people. I want to all cells with dates to be green if they have a date beyond 6 months from today; yellow fill if the dates are within 3 and 6 months from today; and red if they 3 months or less from today. I've tried a few conditional formatting options but I can't seem to get the correct formula needed.
    Thanks for the help!

  13. hello,

    Im trying sort out how many days of each month I worked on a project, but the way im trying to do it is I would like to be able to highlight the whole column and do a formula that would automatically calculate for each month how many days I worked for example:

    3/30/2016
    3/31/2016
    4/1/2016
    4/3/2016
    4/5/2016
    4/5/2016
    4/7/2016

    how many days in april did I work and for each month that year? I have a LOT of dates to go through so this would speed it up.

    thank you!

    1. Have you tried the networkdays formula as this help you remove weekends from the countthe

  14. I am trying to convert date to fiscal month however the CHOOSE function does not seem to calculate ?

    =CHOOSE(MONTH(G4),7,8,9,10,11,12,1,2,3,4,5,6)

    1. Hi!

      The formula is correct. Most likely the problem is with the original date in G4, which is either text or a date in the format that Excel does not understand.

  15. how to convert date 19.12.2016 to 19/12/2016

  16. I was wondering if it would be possible to use the "Month" formula to conditionally format dates which are either one month or two months old? So for example, if the current month is February, I would want dates that fall between December to January to be highlighted. But I want this to go on for a continual basis, so that when I put in some dates next month, January and February will be highlighted. Is this possible with conditional formatting? Many Thanks.

  17. When i subtract two dates like 1/Feb/2016-1/Jan/2016=31 days.
    i want to know that these 31 days come in which month (For Example= Jan'15 month).
    Please help to suggest any formula.

  18. Hello Maria,

    I have a date in a cell (6/12/14). I would like to be able to show just the year in another cell. If the date is before July 1 then the return would be 2014. If the date is after July 1 then the return would be 2015.

    Thanks for any help you could offer.

    Joe

  19. how to get three letter month for example convert December into DEC.

    1. Hello, Medusa,

      You can use this formula in the Helper column to leave 3 first letters:
      =LEFT(TRIM(A1), 3)

  20. Is there a way to clean up How to sum data by month in Excel so I don't need a second 'month' column? And can this be modified to sort data by month and year, ie, multiple years data that is parsed into January , february, etc buckets by year, say a column for 2015 that shows Jan - Dec and another column for 2014 that does the same for Jan-Dec but extracts data from a multi-year column of data?

    1. Hello, Pete,

      Sorry, looks like it's not possible to accomplish this task without parsing data.

  21. I was wondering how to create a formula so that I can see data from a particular month.

    For example - For Budget review, we typically will need to see the previous month's data:
    if Jan, show data in cell G4,
    if Feb, show data in cell G5,
    if Mar show data in cell G6,
    if Apr, show data in cell G7,
    if May, show data in cell G8,
    if Jun, show data in cell G9,
    if Jul, show data in cell G10,
    if Aug, show data in cell G11,
    if Sep, show data in cell G12,
    if Oct, show data in cell G13,
    if Nov, show data in cell G14,
    if Dec, show data in cell G15

    1. Hello, Todd,

      I think this formula should work:
      =IF(MONTH(A1)=1,G4,IF(MONTH(A1)=2,G5,IF(MONTH(A1)=3,G6,IF(MONTH(A1)=4,G7,IF(MONTH(A1)=5,G8,IF(MONTH(A1)=6,G9,IF(MONTH(A1)=7,G10,IF(MONTH(A1)=8,G11,IF(MONTH(A1)=9,G12,IF(MONTH(A1)=10,G13,IF(MONTH(A1)=11,G14,G15)))))))))))

  22. credit date to payment date how to calculate day in the exel work sheet.

  23. I used the formula "Datedif" both for Month and year, it's missed one Month or one Year. Eg. 01/Jan/2014 and 31/Dec/2014 the logical is 12 Months but the answer of the formula is 11 Months (Wrong).

    One More example - Difference below two dated in months calculated with =Datedif , function is 47 as it has missed both the months ( starting and end )

    1/5/2019 3/5/2015 47 ( 5th jan-2019 To 5th March 2015)

    Please help - I want in my formula to include both the months at the time of calculation.

  24. Actual expire date of cheque I want.

  25. I have cheques eg.cheque issue date 29/03/2015 chque is valied for 6 months issue date to future six month how to calculate.

  26. Thank you very much Svetlana, works very well

  27. I am trying to convert a Month into the a date number.
    eg If I write the Month October, I want to convert to the following 1/10/15

    1. Hello Mark,

      Excel formulas cannot convert a month to a date in the same cell. But if you enter October, say in cell A1, you can enter the following formula in some other cell to convert it to the date:

      =DATE(YEAR(TODAY()), MONTH(DATEVALUE(A1& "1")), 1)

  28. I need a formula which returns inception-to-date sum of a specified account code, such that this sum changes whenever a month is selected from a drop-down list. Need help!

  29. I have days as number which will give difference between two dates...
    For example : 01-03-2015 and 03-04-2015. I will get difference as 33 days.
    Now to know this 33 days comes in which month... like 380 days comes in 13th month

    1. (Year(Current Date)-year(previous date))*12+month(Current Date)
      If you have the previous date in A1 and number of days as integer in A2
      Then formula will be
      =(Year(A1+A2)-Year(A1))*12+Month(A1+A2)

  30. Hi, really interesting,
    however also wanted to know if its possible to count how many dates occur in the next 60 days.
    Have a list of certificate expiry dates, and want to know how many in column E expire in the next 60 days.
    Is this possible within excel?

    I've tried all of these;

    =COUNTIF(E11:E1000,">"&DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))
    =COUNTIFS(E11:E1000,">"&"Today",E11:E1000,"<"&"Today"+60)
    =COUNTIF(E11:E1000,August(TODAY())+2)

    and none work/give me the correct answer. trialing the equations and know I have 4 within the next 60 days, each either give me 6 or 0.

    Do you have any possible suggestions? Thanks

    1. Hello, AC,

      Please use the formula below:
      =SUM((DAYS(E11:E1000,TODAY()) <= 60) * 1)

      Note, this is an array formula. Press CTRL+SHIFT+ENTER to enter it.

  31. Hi.
    Im trying to autofill dates from a month..
    example: if i fill the name of month: january.. then my 31 tables has to autofill the dates. those tables has to be connected with the name january..

    i made something for now..
    i put a datenumber example: 1-7-2015.. and the other tables autofills the date with +1 day for the next one...

    but was wondering if i put the month name, will it change too. thx for help

    1. Hello, Memo,

      For us to be able to help you better, please send us the formula you use.

      1. I have a similar problem to what is above. I want to autofill a column with the date number by referencing a column where I will just enter whatever months I want to be filled

        Really appreciate the help

        1. Hello. I have the sampe problem. would u mind to share the steps or formula ? thanks

  33. Hi, may I know how to convert the data below to month ?

    01.09.2015

    I want the answer in Sep'15

    Many thanks.

    1. Hi...

      U can tray this..
      1st u have to convert into the date format to that the particular cell..follow the below ...
      ♦Press -alt+a+e (Text to columns) with select the cell
      ♦Then it should be in default mode or select ◘ Delimited - Next -Then remove all the check mark from all Delimited tab -then enter next- and select date in column data format range and select DMY - then enter Finish....

      After all this your date formats will be change into 9/1/2015

      then u can apply TEXT function to get your ans :-

      9/1/2015 - =TEXT(select the sell,"mmmyy")

      I hope u will get your answer ..

      Thanks and cheers
      Rajesh

      1. Please just replace "." to "/" and than use =EOMONTH(A2,0)

    2. =PROPER(TEXT(A2;"MMM"&"'"&"YY"))

      A2 is the cell where the date is. For Office 2013 use ; in formula. For earlier office use ,

      1. =PROPER(TEXT(DATE (2015;9;1);"MMM"&"'"&"YY"))

  34. when i entr any number in excel cell it will be changed into decimels like 5 is changed as 0.005, but when i put =and then put NUmber, it will not be changed

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