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

  1. Hi
    Hope all will be fine.
    When I apply date format to text "10-06-2023", it converts it to 06-10-2023 or 06-Oct-2023. But I need the result 10-Jun-2023. How to sort it out?

      1. Thnx for the reply. But I have done everything to sort it out but in vain. Then I tried CSV and import process and finally resolved.

  2. Hi - I'm looking to calculate % of days that fall in each quarter based on a start and end date, counting the start date as a day in the quarter and in the total. For example: I have a start date of 3/26 and end date of 4/9. That's 6 days in Q1 (40%) and 9 days (60%) in Q2. Is there a formula I can use to output number of days in Q1 and number of days in Q2? Is there a formula I can use to calculate the % of total days that fall in a given quarter?

  3. Hi! I need to create a formula that inserts the number of the month (1-12) into a cell if the date falls between the 1st and the 15th of the month. If the date falls between the 16th and EOM, I need to put the number of the month (1-12) into another cell.

    Cells in Column A are used to calculate income that is received between the 1st and 15th.
    Cells in Column B are used to calculate income that is received between the 16th and EOM.

    These cells will be used in a formula to determine where to add income amounts on a different sheet within the workbook.

  4. used =TEXT(A5; "mmm") where A5 was a date formated as 2022-06-19 returned either 00 or =TEXT(A5; "mmm")

    I am presumably missing something obvious!

    1. Hi! I don't know what local date settings are used in your Windows. I assume that the date is written as text. To get the date, try these recommendations: How to convert text to date and number to date in Excel.
      You can extract the day, month and year from the text and create a date using the DATE function.

      =DATE(LEFT(A2,SEARCH("-",A2)-1),MID(A2,SEARCH("-",A2)+1,SEARCH("-",A2,SEARCH("-",A2)+1)-SEARCH("-",A2)-1),RIGHT(A2,LEN(A2)-SEARCH("-",A2,SEARCH("-",A2,1)+1)))

  5. Hi I need some help please.

    I need to get extract data from a table of every working day in month, but only that which falls on the last day of the month. The data set contains multiple years worth of data so need it specifically for each month of each year.
    Is this possible?

    Data looks like this:

    Date Value of transactions Number Of transactions Largest transaction
    04/01/2021 47 96 6
    05/01/2021 35 46 12

    So I would need the value of transactions for the last working day of every month for 2021, 2022, etc

    Thanks

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

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

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

  9. 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! :)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  43. 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,"")

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

  45. please help me

    if today 2nd day of current month is monday and 1st date of current month is Sunday then return last month end date in excel.

    Example if today 02-05-2022 is Monday then return last month end date (30-04-2022)

    please help me

    1. Hi!
      To determine your conditions, use the DAY function and WEEKDAY function. Try this formula:

      =IF(AND(DAY(A1)=2,WEEKDAY(A1,2)=1),EOMONTH(A1,-1),A1)

      I hope my advice will help you solve your task.

      1. Thank you Boss..

        i need one more help.

        if today 2nd day of current month is Monday and 1st date of current month is Sunday then return last month end date or if 1st date of current month is not Sunday then return Yesterday Date.

        it's is possible ??

  46. Hi,
    I'd really appreciate your help.
    I have a column of dates eg.01/02/2021. I'd like to be able to have a formula so that in the next column it shows just a month eg. December.
    I'd like this to be worked out like this: if the day falls in the first half of the month (1-15) the month stays the same in the second column e.g. column 1 01/02/2021 , column 2 reads February. If the day falls into the second half of the month 15-31 the second column will move to the next month. eg. 16/02/2021 second column would read March. Is that possible?

    Thank you

    1. Hello!
      Specify the day of the month using the DAY function. If it is the second half of the month, use the EOMONTH function to set the date of the next month. You can get the month name with the TEXT function.
      The formula below will do the trick for you:

      =IF(DAY(A1)<15,TEXT(A1,"MMMM"), TEXT(EOMONTH(A1,1),"MMMM"))

      This should solve your task.

      1. Great piece of code there, Alexander. Thank you for that!

  47. Hello
    I need your help here. I want in a sheet, when I write at A1 the name of the month, starting from A2 to show me the dates. So if at A1 I write january, At A2 to write me 01/01/2022, at A3 02/01/2022 till the end of month. Can you help me in this please?

    Thank you

    1. Hello!
      Use the VLOOKUP function to find the month number. Get the date using the DATE function.
      The formula below will do the trick for you:

      =DATE(2022,VLOOKUP(LEFT($A$1,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),ROW()-1)

      Try to enter the following formula in cell A2 and then copy it down along the column.
      Set the date format in the cell to whatever you need.

  48. hi,
    can u help on this
    for example in one cell I wrote April, May
    and if todays month is march I want to get a notify like active in cell next to it

      1. thanks

  49. Hello,

    Is there a way to get a box to fill to highlight if today is the date in the box (e.g. for birthdays)? I can't seem to figure it out.

    For example, in column L I have their date of birth, in the format DD/MM/YYYY, and I'm hoping to have this flash when today is their birthday? Or if that's not possible, have the column next to it, M, flash when again it is todays date in the column L

  50. Hi, I'm struggling to create a formula and would really appreciate your help.

    So, I want to add a formula to O5 to...

    Count the number of sessions (column D5: D100) held by a specific coach (column C5:C100) within each month (date column B5:B100, then column O4 which states e.g. April 2022 and then each month prior where I can drag the formula along)

    I hope this makes sense.

    Thank you in advance!

    1. Hello!
      If I understand your task correctly, try the following formula:

      =SUMIFS(D5:D100,C5:C100,"John",B5:B100," > ="&DATE(2022,4,1),B5:B100," < ="&DATE(2022,5,1))

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

      1. That's amazing! Thank you so much!

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)