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

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

  2. Hi,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  17. By using countif() I derive the total number of records of each branch as usual I want data regards specific month

    1. Hello Madhavi!
      Use the expression as a condition: MONTH(D1)=4 where D1 is the date cell.
      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.

  18. I use a spreadsheet for monthly budget reports. Each month has a YTD column. The formula typically looks like this: ='[March 2019]MARCH 2019'!E3+'APRIL 2019'!B3
    In this example, April is added to March's YTD totals in that column. When setting up the sheet for 2020, I have been unable to get it to change the whole column of totals. It will only change one cell at a time although I have included all cells.

    1. Hello Gojo!
      Unfortunately, you have not written what you managed to change in your formula and what you did not manage to change. It is not clear enough what exactly is not working.
      Perhaps, there is a problem with the names of the files in your formula, but it is merely my assumption.
      Please describe your problem in more detail and I will try to help you.

  19. I have downloaded a data which is from Jan-2020 to mar 2020, like below:
    05/02/2020 06:09 - It is showing month as May
    1/31/2020 2:55 AM - This one is showing Jan
    1/29/2020 12:27 AM
    1/17/2020 6:47 AM
    1/16/2020 5:39 AM
    1/14/2020 7:46 AM
    06/01/2020 03:41 - This one showing as June 20202.
    I tried converting it into month through text formula, tried changing format through data tab to convert text to column, also manually tried custom formatting but no go.
    Please help me in rectifying this.

    1. Hello Himanshu!
      Please go to Format Cells, choose Number -> Custom Format and set
      mmm-yyyy;@
      to display the date as Jan 2020,
      mmmm;@
      to display the date as January.
      I hope you will find my advice helpful.

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

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

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

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

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

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

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

      1. =DATA.VALue(replace(replace(a2;".";"/");" de ";""))

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

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

  27. Hi,
    Struggled to understand this, =TEXT(A2*28, "mmmm"). Why multiply by 28?

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

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

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

  30. Problem solved:
    I used this-
    =AND(MONTH($A1)=MONTH(TODAY()),YEAR($A1)=YEAR(TODAY()))

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

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

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

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

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

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

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

  36. Please tell me how to convert the the date format from 07/25/2019 to 25/07/2019

    1. Assume your data in A2 Cell
      DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2,1)-1),MID(A2,FIND("/",A2,1)+1,2))

  37. hi
    i have need excel formula for below query..

    a6b8c9fg43gg44rrfd43dfg55
    how to count alphabet and number please suggest

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

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

  40. Hi ,
    I want one formula ,i need count of current date of current month, without using range cell
    Thank you

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

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

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

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

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

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

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

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

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

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

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