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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  21. thank you, it helped

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

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

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

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

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

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

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

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

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

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

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

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

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

  35. Hi,

    I need help in creating a formula giving me the sum of dates (particularly dates in last month) appeared in the column.

    I have list of dates in Column G and need the answer in the column M.

    I am sorry if you have answered the similar question earlier.

    Many thanks for your help in advance!

      1. I have list of data
        Transactions as per client with the transaction date. one client has done several transactions each month. I need the sum of transaction client has done in previous month say (April-22)
        Column B(Clients), Column G (transaction date). If client client A has done 9 transactions total and previous month (April) he has done 3 transactions
        In column M, I need the result 3.

          1. Thank you for your reply.
            I tried the above formula but no luck. Can you tell me what is "A" referred to.

        2. To add the dates are different in previous month

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

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

  38. Hi I have an excel sheet with two columns:
    Suppose this is column A

    A
    4 Y, 5 M, 2 D

    and this is B
    3 Y, 4 M, 1 D

    I want to subtract these two columns. I want a third column which shows the subtraction of A and B. The data is 3 years 4 months 1 days (in this format) so I want the answer to be in the same format in a single cell. Can anyone help me with the solution? what formula to use?

      1. how do i change it?

  39. Hello, so I have quite the predicament. I have 4 columns which have the following:

    Column A: 3/14/2022 (A specific date)
    Column B: 3/11/2022 (Start of 13 day period)
    Column C: 3/24/2022 (End of 13 day period)
    Column D: Needs to return the current Start Date (3/11/2022) and auto-update to (3/25/2022) when the time comes. So basically, I need D1 to show the current start date from column B and have it automatically update to the next start period once reached which is the 25th, and then again after the next 13 period.

    2. I also forgot to mention that Column 1 contains the formula =TODAY()-WEEKDAY(TODAY(),16) which gives me the first date I need to work with (3/25/2022) and the cell to the right of that has CELL+13, so I guess the cell above 3/25/2022 would have -14

  40. "Would like to apply the column with the condition.

    In sheet1, the user will input data.
    Shipping date logic as below:

    If Date (sheet1 column A) is not the current month Period (reference Period) can leave blank.
    If Date (sheet1 column A) is the current month Period (reference Period) force to input something. Example line 9-10.

    The reference table will be changed yearly. "

    1. Hello!
      To compare the month of the date in column A with the current month, you can use a condition like this:

      MONTH(A1)=MONTH(TODAY())

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

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

  43. sir good evening.

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

  44. Very good info ! Thanks !!!

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

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

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

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

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

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

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