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

  1. Hi,
    please i need i support

    I have 2 sheet first one includes the following details:

    employee ID 12346 and leave balance up to end of the year (33)

    second sheet in includes the following details: employee approved leave history

    Emp ID leave start leave end
    12346 20-Jan-23 20-Jan-23
    12346 01-Mar-23 03-Mar-23

    which formula will help to know the how may leave been take per month

    for example i what the sheet to give me

    Employee ID
    number of leave taken in Jan
    number of leave taken in Mar

    thanks in advance.

    best Regards

    2. please i need an urgent support if you can please to the above concern

  2. Hello Expert.
    I appreciate the good work you are doing.
    I have a problem to solve. I have a table to monitor the treatment of patients in the continuum of care. There is a particular column with the Date of the next clinic appointment with varying dates. I want a formula that will return all dates with the current month in another sheet and it will copy all the columns with information in them for the clients that will come to the clinic in the month.

      1. Thanks for the quick response. I found the link you directed me to useful, however, the filter function only works with Excel 2021 upward. My Excel is an old version and is there any other way to go about it using formula.

  3. Hello Alex

    Thanks for this amazing forum and Q&A. I need a quick help on the below. I have below Set of Data and I wanted to get the last Month Name for a particular App Id. As an example for App Id "123456" I am expecting Jan-24 as output and similarly for AppId "987654" expecting Feb-24 as the output. Can you guide how can I get it.

    App Id Month Name
    123456 Jul-23
    123456 Sep-23
    123456 Aug-23
    123456 Jan-24
    123456 Dec-23
    987654 Sep-23
    987654 Oct-23
    987654 Feb-24

    1. In cell B1, write this formula to split the text into ID and date. After that, you can copy this formula down along the column.

      =TEXTSPLIT(A1," ")*1

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

      Hello! In cell D1, write the formula and copy down along the column.

      =TEXT(MAXIFS($S$1:$S$8,$R$1:$R$8,B1),"mmm-yy")

      You can learn more about MAXIFS function in this article: MAXIFS function in Excel – find max value with multiple criteria.

      1. Hello Alex

        Thank you for your quick response. It helped to certain extent but not meeting the expected result. In below table "Result" columns showing the value after applying the below formula. Expected column shows the value I am expecting. As you can see for App Id 123456, value should be Jan-24 and AppId: 987654 Value should be Feb-24. Similarly there will be more App Id and for which I am expecting to get MMM-YY as the result. Its basically expecting for each App ID what is the last month and Yr (MMM-YY) in the month column. Can you help.

        =TEXT(MAXIFS($B$2:$B$10,$A$2:$A$10,C1),"mmm-yy")

        App Id Month Result Expected
        123456 Jul-23 Jan-00 Jan-24
        123456 Sep-23 Jan-00 Jan-24
        123456 Aug-23 Jan-00 Jan-24
        123456 Jan-24 Jan-00 Jan-24
        123456 Dec-23 Jan-00 Jan-24
        987654 Jan-24 Jan-00 Feb-24
        987654 Sep-23 Jan-00 Feb-24
        987654 Oct-23 Jan-00 Feb-24
        987654 Feb-24 Jan-00 Feb-24

  4. Hi Alexander Trifuntov.

    Scenario: Company months overlap between two months; i.e. June sales are recorded from the 26th May until the 25th June.
    Excel will recognize sales from the 26th May until 31st May as May sales.

    Question: Which formula can be used to look at the 26th May until 25th June as "June"?

    This way, company stakeholders will not argue with the data presented.

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

      =MONTH(IF(DAY(A1)>25,EOMONTH(A1,1),A1))

      1. Hi!
        Thank you very much - it worked like a charm! :)

  5. Hi Alexander, I have a question that I don't think has come up when I read your answers. I have the following text in cell A2: 'Credit for April 2023'. Now I am trying to convert the month, April in this example, to a date in 2023 in cell B2; and preferably the last date of that month (YYYY-MM-DD 2023-04-30). I have succeeded in converting the name of the month to number 4 (the 4th month of the year), but I can't figure out how to change the month number into the last day of the month. Maybe I am doing this all wrong, not sure, but I hope you can you help me with this?

  6. Hi sir,

    I have a spreadsheet (exported by Jira) for which every entry has a start date in the format as 15/JApr/23 7:51 AM. To calculate how many days have passed since that start date i input as end date (today's date) in format 21/06/23 and by using the Days360 function i get the age. The problem is that i have to manually change the exported format (of the exported start day) to dd/mm/yy by using find and replace where Jan ->01, Feb->02 etc. or else the Days360 formula will not work.

    Could you think of any alternative to convert the date?

    1. Hi! To replace part of the text, you can use the REPLACE function. For example:

      =REPLACE(A1,4,4, VLOOKUP(MID(A1,5,3),{"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0))

      1. Thank you, sir!

  7. Hi! I have a column with dates. I have another column where I want to display seasonal limits: 2.8 from June to October and 5.7 from November to May. How do I write a formula that displays the correct limit for any year? Thank you!

  8. Please help:
    I have column A indicate: 10 in weeks. How do I capture the 10 (weeks) into number in months?

  9. Hi! I need to be able to find a future date on a set day of the month.

    Example:
    Training Date: 2/27/23
    Launch Date: 4 months forward
    Deadline day (Same day every month): 25th

    I need a formula to auto calculate 4 months forward from the training date, and then the next 25th.
    So in this example, the formula should return 7/25/23.

    If the training date was: 2/23/23, 4 months forward, on the 25th, should return 6/25/23.

    Does anyone know the correct date formula for this?

    1. I think I've got it!

      B5=Training Date
      B9=Deadline day (same date every month/shows a number: 1, 5, 10, 15, etc).
      =IF(DAY(B5)>B9,EOMONTH(B5,4)+B9, EOMONTH(B5,3)+B9)

      Does anyone see an issue with this working?

  10. Hi,

    I have a spreadsheet with 11/30/23 in a date-defined field in A1. In E1 there is an IF statement: =IF(MONTH(TODAY()=MONTH(A1)),"Yes","No")

    The result is "Yes" even though today's month is 5 and doesn't equal the month in A1 which is 11.

    Please help me understand why the result isn't "No" since the logical test is False.

    Thanks so much!

    1. Hi!
      Try this formula:

      =IF(MONTH(TODAY())=MONTH(A1),"Yes","No")

      Also check cell A1. Perhaps there is text and not the date.

      1. Thanks for your help!

  11. Dear Team, I want to present the last 3 months of data , the month should be change dynamically in future. tell the formula

  12. Hi Alex, sorry if theres a real simple solution to this but I can't stop excel from resetting month count once it exceeds 12.

    Eg I'm using =month(I5-G5-X5) to work out the months between the date range. In this example there's actually 14 months but excel is reverting to 2.

    Any ideas?

  13. Greetings Alex, I am searching for a formula solution that will enable me to populate data, by month in a two dimensional array. I am tracking income adjustments over a 12 month calendar year. For example if a salary adjustments are made sometime during April - December, I need to populate the new (adjusted) salary through the rest of the year, while at the same time maintaining the pre-adjustment salaries. In my attempts at a correct formula, pervious salaries (static and adjusted) will take on the new (adjusted) values. I think I can preserve salaries, previous to any adjustments by limiting salary adjustments to the remaining months in the year. I hope this makes sense.

    1. Hello! Your task is not completely clear to me. To ensure clear understanding of your task, can you provide an instance of the input data and the desired result you are aiming for?

      1. Still trying to resolve this issue, Alex. For example, I am trying to adjust income that occurs this month. I can use this formula where O2 is the cell containing the number of the month and the index value is the income from the previous month. E9 contains the adjusted amount (if any) for this month. K15 contains the original monthly income and does not change.
        =IF(O2=5,IF(INDEX(D19:O30,12,4)E9,IF(E9=K15,K15,IF(E9K15,E9,))))

        This formula works OK for the current month. On the other hand, I can't use this formula for other than the current month, i.e. I want to preserve the adjusted income amounts (if any for the previous month). Your suggestions are always welcome.

        1. Hi! I can't check the formula as I don't have your data. But I assume you can change the number 5 to a different month number.

          1. OK, Here is some data. Assume that K15 contains the monthly income of $7000.00 (populated at the beginning of the year [January]. K15 will not change during the year. E9 also is populated with the same monthly income at the beginning of the year as K15. E9 can change as monthly income goes up or down. In May E9 has a value of 8,000. monthly income which means that there was a 1,000. increase in previous months Jan-Ap. Both K15 and E9 are outside of the table. The table contains 12 columns, one for each month, and a cell for monthly income in each column.

            My approach has been to compare the previous month's total income (April) to E9 (8000). if a change (in this case, 1,000) then use E9 for May. IF E9 is = to K15 (7000) then I use K15 for May. If E9 (8000) to K15 then use E9 for May. I am using a formula, in the monthly income cell, for each month in the table. As above this formula works for May: =IF(O2=5,IF(INDEX(D19:O30,12,4)E9,E9,IF(E9=K15,K15,E9))) .

            I can not use this formula for months Jan - April because, for example, O2 (month # = 5 in May) 4 in April column and will return a "False" instead of preserving the original income (7000) for April. Sorry for this long post. Maybe my solution of preserving static and adjusted income through out the year is not the right one.

            1. Hi! Unfortunately, without seeing your data, I can't understand what you want to do. But I don't think you can use one cell with a formula for all months.

              1. Hi Alex, Here is some Demo Data from a partial table.
                Summary Monthly Expense Reports

                Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23
                Miscellaneous Expenses $49.00 $135.00 $300.00 $0.00 $0.00 $0.00
                Grocery $887.00 $435.00 $400.00 $0.00 $0.00 $0.00
                Car Fuel & Maintenance $116.00 $162.00 $0.00 $0.00 $0.00 $0.00
                Dental $0.00 $2,600.00 $1,500.00 $0.00 $0.00 $0.00
                Vitamins $77.00 $0.00 $0.00 $0.00 $0.00 $0.00
                Health & Well Being $109.00 $121.00 $0.00 $0.00 $0.00 $0.00
                Fixed Monthly Expenses $4,278.00 $4,278.00$4,278.00 $4,278.00 $4,278.00 $4,278.00
                Monthly Fees w Annual Due Dates$172.00 $172.00 $172.00 $172.00 $172.00 $172.00
                Member/Card Fees: Ann Due Date$21.00 $21.00 $21.00 $21.00 $21.00 $21.00

                Total Net Monthly Income $7,000.00 $7,000.00$7,000.00 *FALSE$7,142.86** $7,142.86
                Total Actual Monthly Expenses $5,709.00$7,924.00$6,671.00 $4,471.00$4,471.00 $4,471.00
                Monthly Balances $1,291.00 $924.00 $329.00 $4,471.00 $2,671.86 $2,671.86
                *Formula in the cell for April's Total Net Monthly Income: =IF(O2=4,IF(INDEX(D19:O30,12,3)E9,E9,IF(E9=K15,K15,IF(E9K15,E9,))))
                ** Formula in the cell for May's Total Net Monthly Income: =IF(O2=5,IF(INDEX(D19:O30,12,4)E9,E9,IF(E9=K15,K15,E9)))
                Looking for a formula that I can use for both the current month's Total Net Monthly Income and all the other non-current months.

              2. Unfortunately, this information did not help me understand what you want to do. We apologize, but we cannot offer assistance beyond the scope of this blog. The solution to your query requires a more comprehensive approach and cannot be addressed through a simple formula. However, if you have a specific question about a function or formula, feel free to ask, and we will do our best to assist you.

        2. I made a mistake in the above formula. Here is the corrected formula. Sorry for any confusion.

          =IF(O2=5,IF(INDEX(D19:O30,12,4)E9,E9,IF(E9=K15,K15,E9)))

  14. I want to get no of completed months between two dates i.e. 01-06-1990 to 30-06-1990 or 31-05-1990 to 30-06-1990 (completed one months ),
    but when date is 02-06-1990 to 30-06-1990 or 01-06-1990 to 29-06-1990, this is not completed months between two dates ,
    please guide which excel formula will be suitable for my requirement

  15. Thanks for the excellent detail and generous set of examples! I was able to quickly find exactly what I need to convert a number (1-12) to a month name.

    2. In a CSV file, I receive a text value that contains a date & time value in this format: MM/DD/YY HH:MM
      I want to convert it to a month number and full month name in this format: MM -
      Using the function that you provided above to convert a month number to a month name, I created this formula:
      =LEFT(B2,2) & " - " & TEXT(LEFT(B2,2)*28, "mmmm")

      LEFT(B2,2) - Provides a two-digit month number based on the original date & time value
      TEXT(LEFT(B2,2)*28, "mmmm") - Provides the full month name based on the month number

      Examples:
      Original Date & Time Value My Formula Returns
      03/31/23 09:16pm 03 - March
      04/03/23 03:09am 04 - April

  16. Hi, I need to write a function of condition if my date exceeds 15 months of some stated date (noth munt, excatd date/day) that it should be marked as "expired".

    For example started day is 25.8.2022 and from TODAY function is that date passed 15 months.

    If you understand me correctly, please help! :)
    Thank you in advance

      1. You are a life saver! Thank you, it works :)

  17. =COUNTIFS(Jobsites!$S$2:$S$5000,$A5,Jobsites!$F$2:$F$5000,">="&(DATE($B$2,(MONTH(DATEVALUE($B$1&"1"))),1)),Jobsites!$F$2:$F$5000,"<="&(DATE($B$2,(MONTH(DATEVALUE($B$1&"1"))),EOMONTH(DATE($B$2,(MONTH(DATEVALUE($B$1&"1"))),1),0))))

    This is my formula. The date I am referencing is April 2022 (month is written). If I change the EOMonth formula portion to 30, it will calculate correctly. However, as it is now, it is not calculating correctly. Please help!

    1. Hi!
      I cannot check your formula because it contains unique references to your data. I assume you need to correct formula of the month -

      MONTH(DATEVALUE("1"&$B$1))

  18. I'm trying to get a cell to say the name of the month based off a date in another cell. The cell does not contain a year so the formula =TEXT(D56,"MMMM") is not working. Any ideas? Thank you!

      1. I was given a spreadsheet with birthdays but there is no year. D56 shows 05/26 for May 26th.

          1. Thank you!! You are the best!!

  19. I am trying to convert a date to number of months. Can that be done in Excel?

    1. Hi! You can convert the date to the number of months using the MONTH function, which is described in detail above.

  20. Current formula used to count REMAINING number of months and the way I formulated is creating a negative digit but is the numeric I need.. How to get rid of the negative?

    =MONTH(DATEVALUE(cell & "1"))-12

    example: November is giving me -1, January is giving me -11. Would like these to be whole numbers

    2. Figured it out on my own moving the 12 to the front like a nitwit! Thanks anyway. Hope this helps others

      =12-(MONTH(DATEVALUE(cell &"1")))

      November is giving me 1 now :)

  21. What is the importance of "*28" in the formula =TEXT(A2*28, "mmm")?

    Although I got my result right after pacing *28 in my formula, I would like to understand why my formula, was retuning "Jan" instead of "Feb" for a date of 28-02-2023 in cell B2
    The formula that returned a wrong result is =TEXT(MONTH(B2), "mmm")
    The formula that returned a correct result is =TEXT((MONTH(B2))*28, "mmm")

    Thanks for sparing your time.

  22. Hi. I am trying to use the =MONTH(TODAY()) function to show the month as a number in a cell, but I would like the month number 1 to start as April (tax year start). Is this possible to do?

    Regards
    Gareth

  23. Dear experts,
    I am trying to obtain a date value to a cell depending on the date of another cell.
    Practically:
    Cell A1 is a date
    Cell A2 result should be:
    if date of A1 + 8 days is less or equal to 15 of the month of A1, then A2 should show 15th of the month of A1; otherwise it should be date of A1 + 8 days.
    I tried this formula =IF((A1+8<=15);15;(A1+8)) but it is not working.
    I think that I am unable to say that "15"' is the date related to the month of A1.
    Thank your in advance for your help

    1. Hello!
      If I understand your task correctly, use the DATE function to get the desired date

      =IF(A1+8<=DATE(YEAR(A1),MONTH(A1),15),DATE(YEAR(A1),MONTH(A1),15),A1+8)

      1. Thank you very much Alexander, yes you did indeed understand me correctly.

  24. hi

    I have a problem with this formulas

    =IF(B2="","",IF(EOMONTH($J$1,0)>=B2+1,B2+1,"")

    how can I correct it ?

    thx

  25. Dear Experts,

    How I can get the return of last month in same year based on month and year?

    I use this formula:

    =TEXT(EOMONTH('Stock Report fv'!B2,-1),"mmmm-yy")

    To get August -22 but it returns August -23

    Stock Report fv'!B2 = September-22

    Thank you in advance for your support

    Regards

      1. Hi Alexandre

        Thank you for kind and quick reply. In fact what I am trying to do :
        1. I have a stock ledger with many entries from September 2022 till date
        2. I want to create the stock report with columns "opening balance" , " In" , "Out", "Closing Balance"
        3. For Opening balance I used Sumifs formula and one of the criteria I put "<="&EOMONTH($B$2,-1) - sum in & out from the inventory for all previous months

        4. I behaves like that for one item the 1st entry was done in september (40 Pieces), until now the we issues 22 pieces the stock balanec should be 18 as of today. I have Cell (B2) with name of months with year with combo box to select. When I select september -22 openning balance is 18 (should be zero) total received is 40 (which is right) - closing balance is Okay.

        5. The formula I used for opening balance is

        =IF(ISBLANK($A5),"",IF($B$2="All",0,SUMIFS('Stock Ledger'!$M$2:$M$10000,'Stock Ledger'!$A$2:$A$10000,'Stock Report fv'!$A5,'Stock Ledger'!$B$2:$B$10000,"<="&EOMONTH($B$2,-1))-SUMIFS('Stock Ledger'!$N$2:$N$10000,'Stock Ledger'!$A$2:$A$10000,'Stock Report fv'!$A5,'Stock Ledger'!$B$2:$B$10000,"<="&EOMONTH($B$2,-1))))

        A 5 is unique value

        1. Hi Alexandre, Thank you for your reply and support - it was data type mismatch. The combo was populated through Text () - converted the list into date and now it works
          Thanks

        2. Hi!
          I can't check the formula that contains unique references to your workbook worksheets, sorry. If you select september -22 in the combo box, then Excel will automatically convert this text to the date 01-09-2022

  26. In case it helps anyone else:
    I ended up making helper rows, and then the following formula works

    =IF(SUMPRODUCT(--(Sales!$B:$B=$A3),--(Sales!$I:$I=J$1),--(Sales!$H:$H=J$2))=0,"","X")

    --(Sales!$B:$B=$A3) <- this is my purchaser code
    --(Sales!$I:$I=J$1) <- this is the year in the helper row
    --(Sales!$H:$H=J$2) <-this is the month in the helper row

    If all the conditions aren't met, it equals 0, so I wrapped it in the IF function to change the zeros to blanks, and just give me an X when there was a result.

  27. Hello,

    I have a sheet "Development" in which I show how much overtime our employees have made last month, current year. I wanna choose between Jan-Dec.
    I already have this code:

    Range("G1").Value = "Month"

    Range("G2").FormulaR1C1 = _
    "=TEXT(DATE(1,MONTH(TODAY())-1,1), ""MM.YYYY"")"

    But the problem is that only the year doesn't show up when I run the makro. It's just displays:
    "12.YYYY"

    But I want something like that: "12.2022" or "December 2022"
    From our HR Tool I imported all data automatically into a excel table.

    1. Hi!
      In January, MONTH(TODAY())-1 returns 0.
      Use MONTH(EOMONTH(TODAY(),-1))
      Read carefully the article above.

  28. I have a sheet with orders on it, column B has the purchaser code, and column G has the date. Several of our purchasers buy sporadically, so I'd like to be able to visually see which months a purchaser has bought something without having to filter by each purchaser.

    I was hoping to make a section of a second sheet with the purchaser code in column A, and then the next columns contain the possible months/years (November 2021, December 2021, January 2022, etc). I've tried using the month and year functions, but I'm getting value errors ("A value used in the formula is of the wrong data type"). I suspect it's because I'm having the month function look at an entire column, but I need the entire column evaluated. I've checked the formats too. I'm not sure what is going wrong, and perhaps this is a silly way to be trying this anyway, but I need a scatter plot type visual, and I can't get the excel scatter chart to work right, because it views each time the purchaser code shows up as a separate thing.

    =IF(AND(MONTH(Sales!G:G)=J$2,YEAR(Sales!G:G)=$J$1,Sales!B:B=$A3),X,"")

    Sales!G:G is the list of dates (formatted as date)
    Sales!B:B has the purchaser code (as does A3)
    J2 and J1 have the month and year numbers respectively, since it wasn't working with the date there. I've formatted J1 and J2 as both number and general, and neither seems to matter

    The MONTH(Sales!G:G)=J$2 and YEAR(Sales!G:G)=$J$1 portions were modeled after the "How to sum data by month" sumproducts section.

    I also tried the following formula, and it gets the value error as well
    =SUMPRODUCT((MONTH(Sales!G:G)=J$2),(1))

    This also errors:
    =SUMPRODUCT((MONTH(Sales!G:G)=J$2),(Sales!I:I)) - Sales!I:I has more numerical data

    and this errors:
    =SUMPRODUCT((Sales!G:G)*(Sales!I:I))

    but this does not error:
    =SUMPRODUCT((Sales!G:G),(Sales!I:I))
    (so side note, the * in sum product may or may not still work)

    1. Hello!
      To find matches within a range of values, use the MATCH function. Here is an example formula:

      =IF(ISERROR(MATCH(J2,MONTH(Sheet1!G1:G20),0) +MATCH(J1,YEAR(Sheet1!G1:G20),0) +MATCH(A3,Sheet1!A1:A20,0)),"","X")

      Hope this is what you need.

      1. Thanks. Unfortunately, something isn't working quite right. It seems to just be looking to see if each are on the list, instead of looking to see if all 3 conditions show up on the same line.

        These are the dates for 2021, with a letter to represent the purchaser code
        A 11/21/21
        B 12/13/21
        B 12/13/21
        B 12/13/21
        B 12/13/21
        C 12/16/21
        A 12/21/21
        D 12/21/21
        E 12/22/21
        F 12/23/21

        I want to get data that looks something like this
        2021
        11 12
        A X X
        B X
        C X
        D X
        E X
        F X

        1. Here's a second try at what I'd like the data to look like (it let me paste in a tab space, but removed it when I hit send)

          2021
          11 12
          A X X
          B X
          C X
          D X
          E X
          F X

          1. Urg
            Last try (ignore the dashes if they come through)
            -------2021
            ------11---12
            A-----X-----X
            B------------X
            C------------X
            D-----------X
            E------------X
            F------------X

            1. Hello!
              Try this formula:

              =IF(ISERROR(MATCH(J2,MONTH(Sheet1!G1:G20),0) * MATCH(J1,YEAR(Sheet1!G1:G20),0) * MATCH(A3,Sheet1!A1:A20,0)),"","X")

  29. I'm trying to calculate how many months have passed from today's date to a date in the past. (Example: "As of 12/31/2022, how many full months have passed since May 12th, 2022?" Answer "7". Anyone know how to structure that formula?

  30. I am looking to have a cell the says January (1) collect data from a date range the cover 01/01/2022 thru 01/31/2022, by specific groups. Can anyone help?

  31. Hello!

    I want to copy month and year of the worksheet "InTime" from the workbook "Time.xlsx" into the worksheet "ActualTime" from the workbook "New_Time.xlsm".

    Month and year should be displayed in two different columns.

    I did this:
    ActiveChart.ChartTitle.Characters.Text = Format(Range("E1").Value, "mmmm")
    ActiveChart.ChartTitle.Characters.Text = Format(Range("F1").Value, "yyyy"

  32. Hello Sir,

    Thanks for your kind support and guidance as always. I have below date series for the month of Oct'22 & Nov'22 with duplicates dates:

    1 24-Nov-22
    2 25-Nov-22
    3 26-Nov-22
    4 27-Nov-22
    5 28-Nov-22
    6 29-Nov-22
    7 29-Nov-22
    8 1-Dec-22
    9 2-Dec-22
    10 3-Dec-22

    Now I want to pick the latest date of Nov'22 for the serial 7 (29-Nov-22) although in serial 6 it has same date. Would you please help me.

    Thanks in advance.
    Mamun

    1. Hello!
      To find the last date of the month, search for the last day of the month using the MATCH function with the [match_type]=1 argument. Use the EOMONTH function to determine the last day of the month.
      Please try the following formula:

      =INDEX(A1:A10,MATCH(EOMONTH(DATE(2022,11,1),0),B1:B10,1))

  33. Pls how can I automate month and year in a loan calculated format in excel.

  34. Good day.

    Please kindly assist with how to delete month in a bulk dataset of dates and leave only with the year and day only. For example
    2022-10-01 to 2022-01
    2022-10-02 to 2022-02
    2022-10-03 to 2022-03
    2022-10-04 to 2022-04

    Looking forward to see your reply.

    Thanks

    1. Thank you. I have gotten the solution. I’m now thinking how to automate because I have like 4 different excel sheets with almost 1000 data set

  35. Hello, I need help to determine the same day as the end day independently if the Month is 30 or 31 days. So I want to make the 25th of each month the cutoff day, and the current formula I have is =EOMONTH([@[Date Returned]],0)-5 but for moths that are 31 days, it'll show that the date is the 26th. How can I add that to the formula?

    Thanks

      1. Hello Sir Alexander,

        Please how do i convert a whole number like "100" to get the number of days and months in that 100.
        let say 75 = 2months|15days
        100 = 3|months|10 days

        I will be glad to get a solution through mail from you Sir.
        Thanks

  36. Hello,
    I need a help on the formula to automate the Collection of Fee in the Following Scenario:
    Voucher is issued and Due Date is 10 the of Month
    Now Depending upon the Collection Date i.e Current Date ( Today() )

    a) If Collection Date is Between 1-9 of current month Then Collect the Due Fee
    b) If Collection Date is Between 10-20 of current month Then Collect the Due Fee +100
    c) If Collection Date is Between 21-30 of current month Then Collect the Due Fee +200
    d)If Collection Date is 31 of current month or over into the subsequent months Then Collect the Due Fee +400

    1. Hello!
      Use the date functions to create the dates you need and use them as a conditions in a nested IF function.

      =IF(A1<DATE(YEAR(TODAY()),MONTH(TODAY()),10),B1, IF(A1<DATE(YEAR(TODAY()),MONTH(TODAY()),21),B1+100, IF(A1<=DATE(YEAR(TODAY()),MONTH(TODAY()),30), B1+200,B1+400)))

      I hope my advice will help you solve your task.

      1. Thanks Alot Alexander, it worked.

  37. We have the problem that the formula does not work with "mmm" but with "MMM", is this a version thing or something else? Hope someone can help me figure this out.
    Best regards,

  38. I am trying to have a date populate based on the data of an assessment, here are the scores:
    0-9 none
    10-18 Q 90 days
    19-36 Q 60 days
    37-54 Q 45 days

    In first column will be the date completed, then the second column the score, based on that score, will be the date needed for the above information, what formula would be used for that? Notes: Q means every, if person scores 15 then another assessment will be completed in 90 days, if person scores 35, their assessment would be in 60 days.
    Thank you.

  39. I try to make my organisations automated attandance sheet in excel sheet where month start from month 26th to next month 25 th. As like 26th Dec 2021 to 25 th Jan 2022. But hear problem is when I give Feb month my sheet month count from 23 rd date. How is the solutation.

    2. I try to make my organisations automated attandance sheet in excel sheet where month start from month 26th to next month 25 th. As like 26th Dec 2021 to 25 th Jan 2022. But hear problem is when I give month of March my sheet month count from 23 rd date. How is the solutation.

  40. hi, i have a model which have four different periodes in a table each in a cell like : " 01/01/2022 31/03/2022" | " 01/04/2022 30/06/2022" | " 01/07/2022
    30/09/2022" | " 01/10/2022 31/12/2022 "
    and i want to extract the period that refers to our actual date ( todays date 29/08/2022 so we will pick the 3 period ) and put it in a new tab.
    please help me?

  41. thank you, it helped

  42. Hello! I could use some help finishing this formula I am using. I need to formulate when 401(k) benefits start for employees. The employees are eligible after 1 year of service, but on the 1st of the following January or July, whichever is sooner. For example if someone starts on May 2nd, 2022, they will be eligible for benefits on 7/1/2023 (one year later, then the 1st of July since it is sooner than January). I have the following done, but I can;t figure out what to add to get it to calculate to that following January or July 1st.

    =EOMONTH(B3+365,(DAY(B3+365) > 1)-1)+1

    Thank you so much!!!

    1. Hello!
      Use the DATE function to get the dates you need. Compare these dates with nested IF functions.

      =IF(DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)) > DATE(YEAR(A2)+1,7,1),DATE(YEAR(A2)+2,1,1), IF(DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)) > DATE(YEAR(A2)+1,1,1),DATE(YEAR(A2)+1,7,1),""))

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Amazing. You are beyond helpful!!

      2. Amazing. Thank you so much!!!

  43. Hello, kindly assist on how I could get the last entry per month in a range of different dates and months

    1. Hello!
      To retrieve a record from a table, use the FILTER function.
      If I understand your task correctly, try the following formula:

      =FILTER(A2:D11,EOMONTH(DATE($G$1,$H$1,1),0)-C2:C11=MIN(IF(EOMONTH(DATE($G$1,$H$1,1),0) > = C2:C11,EOMONTH(DATE($G$1,$H$1,1),0)-C2:C11,99999)),"")

      G1 - year, H1 - month. Column C - Dates.

  44. Hello!

    Could you please help with an issue I am having. I am using =TEXT(K4,"mmmm") in cell L4 to show the month of the date entered in cell K4, but I would like L4 to show as blank until there is input in K4.

    I hope this explains clearly what I am trying to do.

      1. Thank you, this works perfectly.

  45. Hi Sir..If a cell in Excel contains 4.5 (Four and Half) number which represents number of months. How can i deduct 4.5 from today's date in excel

  46. Hi,

    I wanted to know if it would be possible to have a cell generate the number of days left per calendar month automatically by today's date so that it can auto countdown/reset on the first rather than me having to manually -1 each day.

  47. I have a list of pay days in the month i.e 12th, 15th, 25th LWD
    Is there a way in a column firstly to create the date for the month i.e. 12/07/2022 25/07/2022 and then doesn't matter if separate column amend as to whether it is a workday work out in a particular month if that is a workday

    We already have a formula for the payment to go column - the last one in the example below, which is =WORKDAY([@[Pay date this month]],-2,Category[Holiday Date]) but want to create the date in the middle column without having to manually do from the singular date in the Pay date column

    Pay date PAY DATE THIS MONTH Payment to go
    25 25/07/2022 21/07/2022

    Not sure if possible but your thoughts are welcome - Thank you

    1. Hello!
      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. Give an example of the source data and the expected result.

  48. I have been trying to construct a formula that accomplishes the following.
    Counts the number of entries before a given date (15th of every month)
    If that number is greater than 8 then it produces $250
    If it is less than 8 it produces 0.
    I would like the formula to be usable in any given month since we have separate sheets for each month.

    1. Hello!
      To count the number of entries in a date range, please have a look at this guide: COUNTIF formulas for dates.
      The formula might look like this:

      =IF((COUNTIF(B2:B100, " > = 6/1/2022")-COUNTIF(B2:B100, " > 6/15/2022"))>8,250,"")

      I hope it’ll be helpful.

  49. Hello,

    I would really appreciate your help if you are able to please.

    I have a column containing dates (dates of birth). I would like in the next column an if statement if the individuals birthday is within the next 7 days to show as true.

    How would I go about that please?

    Thank you in advance.

    1. Hello!
      The answer to your question can be found in this article: Using IF function with dates. To determine the birthday, use the DATE function.
      If I got you right, the formula below will help you with your task:

      =IF(AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))-TODAY() < 7,DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)) > TODAY()),TRUE,"")

  50. I hope you can help me with a formula that will return a cell address based on the month number. I may have the first part that looks like this: =MONTH(A1:W1)=MONTH(D40). The D40 holds the number of the current month. The formula will always return a True/ How would I use this to display the contents of a cell for that particular month. The cell for all months appear on the same row and directly under the date column, eg. the cell that I want to display for June (K1) is in K20, for July(M1) cell to be displayed is in M20, etc.. Any help you can provide would be greatly appreciated. Thanks.

      1. Many thanks to you and your team. Your formula combined with the need to enclose it with a CTRL-SHIFT-ENTER was just what I needed. I have had some SQL coding experience but could not have come up with this solution on my own. I took some time to investigate the #N/A error without success. It was only after having a look at your resource article that gave me the final piece. All the best to the team at Ablebits.

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