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

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

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

  3. "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())

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

  5. hi, I want to format dates based on how many months are left before they are due for renewal.

    for eg. column F5 contains the date 19/02/2021, column E5 tells me that there are 12 months before this is due for renewal. How do I write the formulas so that
    a) can mark when its 3 months less than value in column E
    b) when its past the value in column E

  6. I need to calculate rent month-wise based on start and end date, please help
    Rent per month - 3000
    Start date - 14-Jan-2022
    End Date - 22-Aug-22

    How to calculate month-wise rent between start and dates

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

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

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

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

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

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

    Would much appreciate your input - thanks

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

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

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

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

    1. Hello!
      Please try the following formula:

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

      I hope it’ll be helpful.

      1. Thank you, this worked perfectly

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

    1. Hi!
      You can use this formula:

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

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

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

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

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

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

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

    I dunno how to proceed please help thanks

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

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

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

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

  15. Hi there,

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

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

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

    How do I build a formula to achieve this?

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

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

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

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

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

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

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

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

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

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

  20. HI There,

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

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

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

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

    Any assistance would be appreciated.

  21. How to calculate month + 3 days in excel

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

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

  23. Hello

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

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

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

    Thank you

      1. Yes thank you that worked

  24. Hi.

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

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

    please help with formula in excel

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

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

      I hope my advice will help you solve your task.

  26. Hello,

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

    Thanks,
    Prashant S

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

      =EOMONTH(B1,0)

      Hope this is what you need.

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

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

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

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

          I hope this will help

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

  28. Hi,

    I need your help.

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

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

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

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

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

      Remember to set the date format in cell B correctly

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

    Thankyou

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

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

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

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

      1. Hi alexander,

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

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

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

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

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

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

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

  33. Hi Alexander

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

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

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

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

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

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

  35. I need to take a date from this format 3/31/2020 to the "31st day of March 2020". I have NO problem on the March 2020 part but HOW in the world can I get the st part of 31st ??? Also need the abbreviations for all days 1, 2, 3, ..... 29, 30, and 31. Like 1st. 2nd, 3rd, and so on. The Only workaround I can think of is to create a table or chart with all the possible numbers with abbreviations and then use vlookup or the new xlookup.

    Thanks in advance for any help or insight !!!

    1. Hello Carl!
      If I understand your task correctly, the following formula convert to ordinal date format:

      =DAY(A1)&IF(OR(DAY(A1) = {1,2,3,21,22,23,31}),CHOOSE (1*RIGHT(DAY(A1),1),"st ","nd ","rd "),"th")&TEXT(A1,"mmmm, yyyy")
      or
      =DAY(A1)&IF(OR(DAY(A1) = {1,2,3,21,22,23,31}),CHOOSE (1*RIGHT(DAY(A1),1),"st day of ","nd day of ","rd day of "),"th day of ")&TEXT(A1,"mmmm, yyyy")

      I hope it’ll be helpful.

  36. I need a formal that if a month has 31 days it will displace from cell D5 and if then displace from cell D4. the month is in A1. Can you help me, please?

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

      =IF(DAY (EOMONTH (DATE(2020,A1,1),0)) = 31, D5, D4)

  37. Thanks a lot for your information. I have an issue to convert the following text date to a real date type.
    Jan 02, 2018
    Would you help on how I can convert it to date type? I've tried "=date(RIGHT(A2,4) LEFT(A2,3) MID(A2,5,2))", but it doesn't work.

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

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

    1. Hi Sam,

      Thank you for a very good question!

      In its internal system, Excel considers the number 1 as Day 1 in January 1900 (when used together with month format codes such as "mmm" and "mmmm"). By multiplying the numbers 1, 2, 3 etc. by 28, you are getting Days 28, 56, 84, etc. which are in January, February, March, etc. Multiplying by 29 will also work.

      I've also added this explanation to the tutorial for other readers.

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

    1. Problem solved:
      I used this-
      =AND(MONTH($A1)=MONTH(TODAY()),YEAR($A1)=YEAR(TODAY()))
      Now I want the rest of the date should be highlighted in yellow colour. I mean if the month and the year is less than or more than today's month and the year it should be highlighted in yellow colour.

  40. I have different dates in column A1 (1-Oct-19, 15-Oct-19,29-Oct-19, 12-Nov-19, 26-Nov-19, 10-Jan-21 and so on). I want to highlight only the current month in green and the rest of the month in the red. I mean before and after Jan in the red and Jan (Current Month) in green.

    1. Example 1. Highlight dates within the current month
      =MONTH($A2)=MONTH(TODAY())
      Through this, if year changes it is highlighting the same month also. How can I highlight the date within the current month and year?
      10-Jan-20, 15-Jan-21
      It will highlight both cells. When the year is 2020, it should not highlight the year 2021.

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

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

  42. When I am using TEXT formula for the date 01-10-2019 and i use =Text(A1,"dd/mm/yyyy") in b2 i will found 10/01/2019 how can i fix it

    1. Hi Jayesh,

      Just use the desired format code in the format_text argument. For example, to display the date as 01-10-2019 (where 01 is the month and 10 is the day), use this format code "mm-dd-yyyy":

      =TEXT(A1,"mm-dd-yyyy")

  43. I need formula for Increment of salary
    Conditions are as follows if joining of employee within jane to june then increment month should be Next jan if within july to december it should be Next july

    1. Hello,
      I Need a formula for every month days of number ; if i text a any name of month and then automatically changed number of days ...
      as like I am writing January and then automatically changed number 31 & February changed to 28 etc ....

  44. Hi,

    Thanks for the great info. In the section How to sum data by month in Excel, your example with the sumproduct formula returns a #value error for me when I use your data. Is there a mistake in that formula?

    1. Hi Jeremy ,

      I have just retested the SUMPRODUCT formula, and it works fine for me. It's difficult to say what the problem may be without seeing the actual worksheet. Try to use it on a new sample sheet with just a few data entries. Does it also return an error?

      1. Thanks so much for your reply. I'm so sorry - I had a typo on one of the dates. It works now.

        But, what I am trying to do on my spreadsheet is to get a similar calculation where the dates are simply 'January, February...' That gives me the #value error again. Would you have a solution for that, please?

        1. Jeremy,

          If the names of the months are entered as text, then you can convert them to numbers by using this formula. And then, use a simple SUMIF formula to add up the amounts for the desired month.

          The same result can be achieved with this formula:
          =SUMPRODUCT((MONTH(DATEVALUE($A$2:$A$15&"1"))=E2)*($C$2:$C$15))

          Where A2:A15 are the month names, C2:C15 are the numbers to sum and E2 is the number of the target month.

  45. Goodday.

    i have a question. I have set of dates in a year (cell D1 to D20). I need to count how many days in specific month. Right now, I have use formula COUNT(IF(MONTH(D15:D17)=1,1)), to count how many dates occur in January and i use CTRL+SHIFT+ENTER.

    My problem is, this formula works for february to december, but not for january. If i key in this formula, it will count all the cells eventho its empty unless i key in dates not in january. For example, D1 to D20 is about 20 cells, if i key in this formula, it will give me 20. If all the 15 of the cells dated january, and another 5 cells is empty, it will count as 20. And if the 15 cells dated january, and another 5 cells is dated february or other months, it will count as 15.

    please help me to solve this problem.

    1. Elly:
      When I want to count occurrences of a date or how many times a date between two dates occur in a list I use this formula:
      =COUNTIFS($O$11:$O$22,">=9/1/2018",$O$11:$O$22,"<=9/30/2018")
      Then I label an adjacent cell with the appropriate date.
      You'll need to enter the dates you want, but you would have to do that using yours, too,

  46. Hi Master,

    How to convert e.g; Jul-04-2018 to 04-07-18. Thank you.

    1. Dan:
      Try to right click on the cell containing the date and select Format Cells then from the list click Date then select the date format you want to use.

  47. Project start date 11-May-2018. Project completion is 15 months from start date. How to calculate in DD-MMM-YYYY format.

    1. Sumit:
      You can use EDATE to calculate dates that fall on the same day of the month as the date you are interested in.
      For example, in your case where 11-May-2018 is in A1 it would look like this: =EDATE(A1,15) returns 11-Aug-2019.
      EDATE is useful for loans or payments of various types that mature or are due on a specific date.
      If you want more than the month you can use:
      =DATE(YEAR(A1),MONTH(A1)+15,DAY(A1)) and then add a date in the past or future as this formula shows with +15 in the month spot. Past dates would require a - sign.
      Remember to format the cells in the date format you are comfortable with. They have to be a date, not text. Excel has a built-in date that formats the cell to display dates in the way you want. Right click on the cell, choose Format Cells then select the Date option form the list and you'll see all the various ways Excel can display your date. If that doesn't work go to the Custom option in the Format Cells list and you'll see more options to display numbers, dates and times.

  48. I do have a column X "Due Month" and another "Task completed" .I want to do a vlookup whereby if the referenced cell in task completed column is "yes" then then vlookup should increment the month +1. Is that possible?

    1. Hello, Fahim Idha,
      Please try to add a helper column to your table and enter the following formula into it to increase a month by 1:
      =IF(Y1="yes",X1+1,X1)
      Then just copy the data from the helper column and use the Paste Special -> Values option to replace the values in Column X.
      Hope this will help you with your task.

  49. I have a date of say 20170501 in cell A1, and need B1 to show the end of the month of whatever month is in A1. So in this instance it would need to show 20170530.....if A1 was 20170330 it would need to show 20170331....and so on. Is this possible?

    1. Hello, James,

      enter the following into B1:
      EOMONTH(A1,0)

      Don't forget to change the format of B1 to Date.

      You can learn more about this function here.

      Hope it helps!

  50. I'm using =datedif(A1,B1,"d") formula to calculate the days of the month but when I put the start day 10/1 and the end 10/31 counts 30 days and I don't know how to make to count 31 days that is what I need. I'm working in a foster care agency and to pay the providers I need to calculate exactly the days that every month has. Please if there is another formula could you share with me.
    Thank so much.

    1. Use formula =datedif(A1,B1,"d"+1 to get the desired output.

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