+ Reply to Thread
Results 1 to 17 of 17

Formula to show number of months (1st and last month inclusive) between 2 dates

  1. #1
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Formula to show number of months (1st and last month inclusive) between 2 dates

    Hi,

    I've been trying to sort a formula to work out the number of months between 2 dates. The number must include the 1st month and the last. It also must be able to work between years. I'll give some examples

    1/5/14 - 1/9/14 = 5 months
    1/10/14 - 1/4/15 = 7 months
    1/5/15 - 1/4/16 = 12 months

    I've come close with some furmula's I've found googling but none seem to work quite right.

    Any help will be much appreciated.

    Ant

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    Datedif_____________________

  3. #3
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    Try this..

    =DATEDIF(A1,B1,"m")+1
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,038

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    If your dates are real excel dates (not just texts) and are in A1 start B1 finish:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,038

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    PS. With datediff remember to format your cell as general (exlcel will try to automatically format it as date ) and it will be probably different than you need if dates are not always beginning of month. try:
    15/5/14 - 1/9/14 - shall it be still 5 months? if yes - use my solution if 4 use datediff

  6. #6
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,261

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    Assumed your Start Dates in A1 and End Date in B1:

    =ABS((YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1))+1

  7. #7
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,038

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    Hi,
    If indeed "Start Dates in A1 and End Date in B1" then ABS is not needed.
    Only when "there are two dates in A1 and B1, one of them is start, and the other is end" ABS would be really needed.

  8. #8
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,261

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    Quote Originally Posted by Kaper View Post
    Hi,
    If indeed "Start Dates in A1 and End Date in B1" then ABS is not needed.
    Only when "there are two dates in A1 and B1, one of them is start, and the other is end" ABS would be really needed.
    Yes Im miss that one, no need start date and end date, thanks

  9. #9
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    Brilliant. I'll be sure to try some of these suggestions tomorrow. Thanks you all, I'll let you know how I get on.

  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    Quote Originally Posted by Kaper View Post
    =(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)+1
    You can get the same result in all cases using a modified DATEDIF, i.e.

    =DATEDIF(A1-DAY(A1)+1,B1,"m")+1
    Audere est facere

  11. #11
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    EXAMPLE SHEET - DATES.xlsx

    I am struggling to make any of these formulas work. I have attached an example sheet to demonstrate what it is I am trying to achieve.

    Thanks all,

    Ant

  12. #12
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    what doesnt work? daddylonglegs offer gives the results shown in col J
    =SUM(S2-(G2*F2)-(R2*F2)) gives #value as col g is text and not a number
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  13. #13
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    I apologise. It does work. Thanks very much everybody for your help.

  14. #14
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    Is there any easy way to make it so nothing shows in any of the cells until data is entered on that line. At the minute it shows #NUM in the F J P & S cells.

  15. #15
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,038

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    For F2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    (sum is not needed here)
    and similar approach to other cells
    So for instance J2 (sed my version, but of course you can use whichever you want):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    etc.

  16. #16
    Registered User
    Join Date
    08-30-2019
    Location
    Pluto
    MS-Off Ver
    13
    Posts
    1

    Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    "Assumed your Start Dates in A1 and End Date in B1:

    =ABS((YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1))+1"

    5 years later, this answer worked for me, though in my case I didn't want "first month inclusive" so I removed the plus one.
    My requirement can best be stated as "I want to know how many 1st of the month's there are between two dates."
    Last edited by jdrans; 08-30-2019 at 01:10 PM.

  17. #17
    Registered User
    Join Date
    12-17-2020
    Location
    US
    MS-Off Ver
    Microsoft Office Professional Plus 2019
    Posts
    1

    Post Re: Formula to show number of months (1st and last month inclusive) between 2 dates

    You can do the following which will work:

    If in A1 you have the first date and in A2 you have the subsequent date:

    =month(A2)-month(A1)+1

    eg. If you want to know the number of months inclusive of the starting month for 2/1/2019 and 7/1/2019 then

    =month(A1) will result in 2
    =month(A2) will result in 7

    So you'll get =7-2+1=6

    This seemed easiest to me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using SumProduct for dates inclusive of Year to Date, Month to date, Quarter to Date
    By cartica in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2014, 04:22 PM
  2. Replies: 8
    Last Post: 02-20-2014, 05:46 PM
  3. [SOLVED] Formula to get the inclusive dates in between start and end date
    By krazyhype19 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-23-2014, 08:36 AM
  4. [SOLVED] Sumif formula for inclusive Dates
    By Yari1986 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2013, 05:08 AM
  5. Replies: 0
    Last Post: 05-12-2011, 12:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1