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 5. Total comments: 480

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

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

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

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

  2. HI

    i want to know how to end my month dated when i am using =today()

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

  3. sir good evening.

    in number 0-6-0
    come date format like 0-6-0

  4. Very good info ! Thanks !!!

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

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

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

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

  9. hi
    i have 2 questions please

    #1
    my worksheet has a column in which there are months of multiple years ( 2010-2020)
    i want to select a rows of data associated with 1 particular month of a particular year
    so all data from , for example, February 2020, but not any other months or years.

    #2
    link, export,connect this to a new worksheet in the same workbook.

    and how to do this from 30 separate worksheets?
    is is best to create a macro to use a search and retrieve function?
    or there a simpler method?

    big thanks

    1. Hello!
      To select data for one month of the year in a worksheet, you can use Advanced Filter.
      To select data from a large number of worksheets, I recommend first combining the data into one sheet. To do this, you can use the Copy Sheets tool from the Ultimate Suite for Excel. Then apply the filter you want to that data.
      You can install Ultimate Suite for Excel in a trial mode and check how it works for free.

  10. I have a data set that is pulling from multiple sheets in which more data is being added/updated to month columns in those sheets. I have a column in my data table that shows how many months of the year have been worked thus far. As of right now we have to manually change that column every month. Is there a formula that can be written that would allow that column to update automatically based on data being entered into the "month" columns in the other sheets?

    1. Hello!
      The information you provided is not enough to understand your case and give you any advice. You haven't written what data you enter into your spreadsheet. It may be necessary to use the MONTH function to extract the month number.

  11. I need a formal to use the COUNTIFS formula by reading / looking the month which is in date format.

    Suppose i have column having dates (from Jan to Dec), requirement is to count the data of another column for particular entire months.

  12. I am trying to get a formula to calculate a monthly revenue. The job may run more than a few months but when I report I want to see the historical revenue - I think I maybe have to create several formulas an then join them up.
    Example of job and monthly report
    Job No. Job start Job End Value Per day value
    25 05/01/21 30/03/21 10,000 119.05

    Jan Report
    Job No Revenue
    25 3095.24 (26 days * 119.05)
    Feb Report
    Job No Revenue
    25 3333.33 (28 days * 119.05)
    Mar Report
    Job No Revenue
    25 3571.43 (30 days* 119.05)

    Would much appreciate your input - thanks

    2. In essence, I guess what I need is a way to calculate how many days are in each month between 2 dates in order to multiply that by the daily rate.

      1. Hi!
        To count the number of days in a month (for example, 1 month), use the formula for the difference between the maximum and minimum date in this month

        =MAX(D1:D100*(MONTH(D1:D100)=1))-MIN((IF(MONTH(D1:D100)=1,D1:D100,"")))

  13. Hi!

    I'm trying to pull data from cells only if the cell is in a certain month and is within a certain state and I've hit a wall (feeling incompetent asking, haha).

    Column A - Item X identifier (not needed)
    Column B - cost for X for 1 month (not needed)
    Column C - additional cost for X for 1 month (not needed)
    Column D - Date (example 1/21/18)
    Column E - Length of time (days) with item
    Column G - Price per day per item
    Column K - Is it in X state (true or false
    Column H - Total amount of $ for entire period with item (formula =E2*G2)

    I want to be able to say if date in D is within Jan ( or feb, march, April, etc) and is within X state (or not in X state) then multiply totals of E2*G2 and provide total amount for entire month in one cell.

    Hope that makes sense. You're amazing for just having this space to ask. Thanks.

  14. I want create a formula for a date range and show the month and year.
    For example:
    Date reported 21 May to 20 June = June 2021
    Date reported 21 June to 20 August = Aug 2021
    Date reported 21 August to 21 September = Sep 2021 and so
    Thank you

    1. Hello!
      Please try the following formula:

      =TEXT(D1+(DAY(D1) > = 21)*15,"mmm yyyy")

      I hope it’ll be helpful.

      1. Thank you, this worked perfectly

  15. Cell 1 Contains 04/21,05/21
    In cell 2 I want Starting date of the first and ending date of 2nd month
    EX: 01/04/21 to 31/05/21
    and if Cell 1 Contains 04/21,05/21,06/21
    Ex: 01/04/21 to 30/06/21
    Please

    1. Hi!
      You can use this formula:

      =DATE("20"&MID(A2,4,2),LEFT(A2,2),1)

      =EOMONTH(DATE("20"&RIGHT(A2,2),LEFT(RIGHT(A2,5),2),1),0)

      You can learn more about DATE function in Excel in this article on our blog.

      1. Date Sheet Nos
        01-Apr-21 4511
        02-Apr-21 4512
        03-Apr-21 4513
        04-Apr-21 19501
        05-Apr-21 19502
        sir, i want 4511 to 4513 from 01/04/21 to 03/04/21 and 19501 to 19502 from 04/04/21 to 05/04/21 if sheet Nos changed automatically return accordingly in a particular cell accordingly based on formula please help.

          1. Hi sir,
            1).There is a 50 pages book with page numbers like 4501 to 4550, per page refers per day i.e.,01/04/2021 to 20/05/2021 and new book continuous with page numbers from 19501 to 19550 and date from 21/05/2021 to 09/07/2021.
            2). In a particular date period like 01/04/2021 to 31/05/2021, i want like this "The trips entered in trip sheets from 4501 to 4550 (01-Apr-21 to 20-May-21) & 19501 to 19511 (21-May-21 to 31-May-21)". I used index, match, date and EOmonth functions in that cell by the result it comes like this "The trips entered in trip sheets from 4501 to 19511 (01-Apr-21 to 31-May-21)".
            3). In a sheet, I have a table with columns like Date column and Page Numbers column. I want it automatically comes with the formula. Please help me sir, I tried so many ways but I failed. I think my problem is in detail. Thanking you sir.

            1. in Sheet1, A38 & A39 Cells contains data like this
              A38) 1.Number of Kilometers run during the month of May-21
              A39) 2.Number of days used from 01-May-21 to 31-May-21
              In A40 cell i get the result as
              11. Certified that the trips entered in trip sheets from 4541 to 19521 (01-May-21 to 31-May-21). by using the below formula
              Formula I used ="11. Certified that the trips entered in trip sheets from "&IFERROR(INDEX(Tours!F:F,MATCH(DATE(YEAR(MID(A39,30,9)),MONTH(MID(A39,30,9)),1),Tours!B:B,0)),"")&" to "&IFERROR(INDEX(Tours!F:F,MATCH(EOMONTH(RIGHT(A38,6),0),Tours!B:B,0)),"")&" ("&RIGHT(A39,22)&")."
              In Tours Sheet Column B contains dates, Column F contains Page numbers
              by the result what I want is 11. Certified that the trips entered in trip sheets from 4541 to 4550 (01-May-21 to 10-May-21) & 19501 to 19521 (11-May-21 to 31-May-21).

              1. Hi!
                Your explanations are not very clear. Formulas contain links to your data, which I do not have. Therefore, I cannot verify their work.

  16. 30/06/2021, 1:37 pm
    i want this to be converted in month year
    ex;- Jul/21
    =TEXT(value,"mmmm/yyyy")
    applying this formula I am not getting

  17. 1.Column A is names,
    2.Column B is due date,
    3.Column C is END OF TERM and
    4.Column D is Restructured until month(text)

    EX:
    A. Excel
    B. 06/01/2021
    C. 09/01/2021
    D. JULY
    I want to conditionally format these by NAMES highlighted when their DUE DATE is a month away from COLUMN C or D.
    =IF(B2=(C2-30), AND/OR
    =IF(B2=(D2-1),

    I dunno how to proceed please help thanks

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =DATEDIF(B1,C1,"m")>=1

      Use this formula in conditional cell formatting as described in this article.
      I hope my advice will help you solve your task.

      1. Noted on all and thank you!! :)

  18. Hi there,

    I've very quickly browsed through your table of contents so I apologise if I've missed what I'm looking for.

    I'm looking to return a numerical value from on the last day of each month.

    Each day has a numerical value
    The date range is a daily range which spans more than 1 year.
    I wish to summarise the returned data by year and month.

    How do I build a formula to achieve this?

    1. Hello!
      To return a list of values for the last day of each month, use the FILTER function:

      =FILTER(A2:B6,A2:A6=DATE(YEAR(A2:A6),MONTH(A2:A6)+1,0))

      I didn't understand what exactly you want to summarize.

  19. Hi Alexander, you help me with if i have the number of years and months how can get the first date,month and year for that range.
    Ex. if i want the calculate what the date was from 04 months from today, it should be 01-01-2021

    how can i apply that into excel ? if i add year and moth on 2 different cells and it has to return the date. considering excel date as today

  20. I want next month target to be automatically generated no matter what time of year we look at it. (target getting from table)

  21. I need to a formula for convert different date's of Month name with year

  22. I have expiration date and I need to find out expiration range like 0 to 3 Month or 3 to 6 Months in excel

  23. This is the date format ( 20201101 ) which I have right now. Tell me how to get my month name in Text Eg, "Jan"

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =TEXT((--RIGHT(A2,2))*28, "mmm")

  24. HI There,

    I'm not incredibly advanced on excel and am having some trouble, not sure I can adequately explain my requirements.

    I have a large table of information that requires 3 sets of conditional formats that will highlight the entire line if affected by certain date ranges. And I can't figure it out.

    The cell I am working with is 'Shipment Period' which is based on the whole month.

    I need all line data, that:
    1. Falls before (past date) the current month e.g. 2021-02; to be highlighted pink/red
    2. Falls during the current month; to be highlighted green
    3. Falls after (future dated) the current month; to be highlighted orange

    Any assistance would be appreciated.

  25. I need to a formula
    that s will be start month from 16th and end of the next month 15th days
    For example 2/16 to 3/15.

    1. Hi,
      The information you provided is not enough to understand your case and give you any advice. What source data is used for this task? The expected result is two dates or text? Please describe your problem in more detail.

  26. How to calculate month + 3 days in excel

    1. Hello!
      If I understand your task correctly, check out this article to learn how to add days to date in Excel.
      If this is not what you wanted, please describe your problem in more detail.

  27. i want to see if cell a2 show 23.11.2020
    than i want to see cell b2 show 23.12.2020 how to write formula

  28. Dear Mam,

    When we calculate no. of months for by DATEDIF function, result obtained is incorrect.
    Please advise & suggest necessary formulas.

    Start date-1-July-21 (A1)
    End date-31-Dec-21 (A2)
    Formula =DATEDIF(A1,A2,"M")
    Result from function=5
    Correct result=6

    Thanks

  29. I just want to know how to change my date to April 1st

  30. Hello

    I have a series of dates in column B and a series of numbers in column L. I want to add the numbers when the month is October. I thought of

    =SUMIF(B2:B89,"MONTH=10",L2:L89)

    but it gives 0. What is the way to do it.

    Thank you

      1. Yes thank you that worked

  31. Hi.

    what function to used for converting 15-Oct-20 to Oct'20?

  32. hi

    i have numbers in a columnlike 1,2,6,7,,4,657,4986,343,113,4245, in another column i want a oformula whichh will find the result of number of months against the days

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? How are these numbers and dates related? What result do you want to get? Give an example of the source data and the expected result.

  33. Dear sirs, can I ask for a wee bit of your expertise since my own is not enough in this case?
    I am looking to do comparisons between quarters during the year but with the closing balance date fixed from previous year. Let me explain:
    E.g. QUARTER 4(b)=2020-12-31, QUARTER 3(c)=2020-09-30, Closing balance (QUARTER 4(a))=2019-12-31.
    I have created formulas for Q4(b) and Q3(c) (linked to another date cell with the help of your EOMONTH-functions on this page) respecively so that when Q4(b)=>Q1(b), Q3(c)=>Q4(c). But, i want the Closing balance date Q4(a) (ie. 2019-12-31) to remain the same during the year UNTILL the new accounting year starts. So when Q1(b)=2021-03-31, Q4(c)=2020-12-31 and Q4(a)=2020-12-31.
    In words, Q(b) and Q(c) change during the year where Q(a) remain the same with previous year's closing date, and it's only when the Q(b)/Q(c) year change as Q(a) changes closing date one year forward.
    I find the Q(a) value quite tricky to solve.

    Thank you and regards,
    Daniel

  34. There are different dates in excel in each cell. if date is 1st of any month then that should be changed to last date of previous month and if date is 2nd or any other from 2 to 31 then the date should change to last date of that month.

    please help with formula in excel

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(DAY(A1)>1,EOMONTH(A1,0),EOMONTH(A1,-1))

      I hope my advice will help you solve your task.

  35. Hello,

    May I know how to extract the last day of the month if there is only year and month mentioned in general format? For example, 2020-06? The answer I am looking for is 06/30/2020?

    Thanks,
    Prashant S

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =EOMONTH(B1,0)

      Hope this is what you need.

  36. Hi,

    I am trying to calculate and convert the current month to "currentmonth" using today date and month.

  37. 01/04/2020, 10:15:00
    how to convert this in to month

      1. i have tried but i can't get the result, can you please give me the formula which will help me to get month when it appears in this format. 01/04/2020, 10:15:00

        1. Hello!
          You did not say anything about this, but I can assume that your date is recorded as text. Therefore, you need to remove the comma from it and convert it to a date. And then apply the MONTH function:

          =MONTH(DATEVALUE(SUBSTITUTE(D1,",","")))

          I hope this will help

          1. Thanks,
            So, there is no option without removing , in this format.

  38. Hi,

    I need your help.

    Column A = Completion of project (Date format: 01-Jul-2020)
    Column B = Invoicing Month (Month format: July 2020)

    Items completed within 1st - 25th of every month must be invoiced in that month itself and items completed from 26th-31st is to be invoiced the next month.

    Any ideas how I can set a rule so Column B is generated based on the dates automatically?

    1. Hello Sammy!
      The formula below will do the trick for you:

      =IF(DAY(A1)<26,A1,EOMONTH(A1,0)+1)

      Remember to set the date format in cell B correctly

  39. Correction: Martch duration = 22 days, April duration = 22 days, May duration = 11 days. Thanks.

  40. Hi Alexander,
    I have data as below:
    Task start_day end_day duration, d
    Task1 01/03/2020 15/05/2020 55
    I have to show task duration by month in the chart. How can I get data needed for chart? I need: Martch duration = 20 days, April duration = 20 days, May duration = 15 days. Thanks.

    1. Hello Ruta!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

  41. Sry I forgot to add the formula I'm using. =SUMIF(O6:O24, AN6, AJ6:AJ24)

  42. Hello,
    I have used the formula above and only changed the cells, however the result sum to $0. Do the cells need to be together to work because these are not.

    Thankyou

  43. in case if you got 00 your computer's language setup is the problem
    use this: =TEXT(A1;"[$-en-EN]HHHH")
    en-EN ---> add here the language's code you want to see
    HHHH ---> the version of MMMM (month full lenght) in your computer's language
    Your version can be checked in Format Cells - Custom ---> last two options

  44. I am doing a monthly billing spreadsheet where I have listed income by each month, this is along side the bill,amount, and due date. At the bottom I am wanting the extra money left from each month calculation, needing it to register which month it is and bring that months income amount - the current months bills. Example below.
    Due Date Bills Amount Month Wks Income(mthly)
    7th Car $365 January 4 $2000
    8th Netflix $13 February 4 $2000

    Total Monthly Expenses $478 Leftover ____?????_____

    1. Hello LeAnn !
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred.How is 478 calculated? Give more an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  45. hello. i have a question and it may be stupid if this doesn't exist in excel. :D
    is there a command/formula to change all the written month (ex: January) and change it to the next month (ex: February) rather than changing/typing it one by one. i wanted to make the changes faster since the 2 sheet file will be used for the entire year except the months have to be changed.

    thank you for your time. :)

  46. I need a format for if a date has is in the last month to return another cell and if its not in the past month to return blank

    1. Hello Ben!
      Using cell formatting, your problem cannot be solved. If you need a formula, describe in more detail all the conditions. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  47. I am a registered dog breeder and I would like to create in excel a schedule for the litter. I cannot breed two litters in 18 months. How do input my last litter so when I look at it when the right month and year to breed her. I only have 3 dogs.if it's under this month, to show in red. Anyone can help?

    1. Hello!
      I’m sorry but your task is not entirely clear to me. You need to use conditional formatting. What data do you use in the table?
      For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  48. Currently using this [WEEKNUM(TODAY(), 1)-WEEKNUM(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 1)+1)] in a countif formula. However what would would i do if i wanted to have a month to date look. Ex Week 1 would just be week 1. and week 2 it would be week one + week two and so on.

    1. Hello!
      Your formula calculates the week number in the current month. But I don’t understand what else do you want to calculate? Explain in more detail so that I can help you.

      1. Hi alexander,

        Correct. Its currently calculating to look at the current week. IN my scenario I have bunch of dates all throughout the Month. And depending on the week i am in, i calculate how many of those dates fall within the same week that were are in. What i am wanting to do is to adjust that to give me a running total. For example, if i was in week 2 the current formula will just looks at all the dates that are in the second week and count those. Instead I'm wondering if we could alter the formula to look at all the past weeks of the month as well. So if we are in week 2, the formula would Look at week 2 + week 1. I hope i was able to clarify this.

        1. Hello
          If I understand your task correctly , if the date is written in cell C1, and the formula

          = WEEKNUM (C1,1) -WEEKNUM (DATE (YEAR (C1), MONTH (C1), 1), 1) +1

          in cell D1,
          then the condition for the counter of the desired dates

          = COUNTIFS($D$1:$D$99, D1) + COUNTIFS($D$1:$D$99, D1-1)

          I hope this will help, otherwise please do not hesitate to contact me anytime.

  49. Hi Alexander,
    I want to know the formula to get the particular day of every month in the given data.

  50. Hi Alexander

    This was very helpful for my first part of the task. I have now taken the month from a date and shown it as the Month in text. I now want to take that result and have it sequence monthly for 24 months across a row, but I am having trouble reference that result and indexing it across the columns. Can you advise?
    I am a beginner in Excel, so trying to learn what I can as I need it.
    Thank you!

    1. Hello Glenn!
      If I understand your task correctly, in cell A1 is your start date. In cell A2, write the formula

      =TEXT(EDATE($A$1,COLUMN()-1), "mmmm")

      After that you can copy this formula right along the row.
      If there is anything else I can help you with, please let me know.

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