+ Reply to Thread
Results 1 to 6 of 6

Formula for number of months for specific year between two dates

  1. #1
    Registered User
    Join Date
    12-12-2017
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    4

    Formula for number of months for specific year between two dates

    Hi,

    For my interest calculations, i need to know the numbers of months for the specific year, which is valid between two dates.

    Start Date : 01-May-2016
    End Date : 10-Apr-2021

    What formula/function i should use, if i want to know number of months for 2017?

    eg.

    if i select the year, i should know the number of the months for the respective year, like below

    Year - Months
    2016 - 7
    2017 - 12
    2019 - 12
    2021 - 4

    Could any one please advice me to find this?

    Thanks in advance.

    Regards,
    Kalaiselvan

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Formula for number of months for specific year between two dates

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for number of months for specific year between two dates

    With the dates in A1 and A2 respectively, try this:

    =(12-MONTH(A1)+1)+MONTH(A2)+(YEAR(A2)-YEAR(A1))*12
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Formula for number of months for specific year between two dates

    Assuming start date in A2 and end date in B2 try listing the years, e.g. 2015, 2016 etc. in C1 across and then use this formula in C2 copied across

    =IFERROR(DATEDIF(MAX($A2-DAY($A2)+1,DATE(C$1,1,1)),MIN($B2,DATE(C$1+1,1,0)),"m")+1,0)

    That will count any part of a month as a whole month. I get 8 for 2016 given your example, see attached
    Attached Files Attached Files
    Audere est facere

  5. #5
    Registered User
    Join Date
    12-12-2017
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    4

    Re: Formula for number of months for specific year between two dates

    Hi All,

    Thanks for your responses.

    @ daddylonglegs,

    Thank you so much.. I got what exactly i needed from your response .

    KalaiP

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula for number of months for specific year between two dates

    Or this
    Enter in B2 and copy across
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E F G
    1 2016 2017 2018 2019 2020 2021
    2 5/1/2016 7 12 12 12 12 8
    3 4/10/2021
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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. Calculate number of months in a specific year between two dates.
    By chickynee in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-09-2019, 07:29 PM
  2. [SOLVED] Calculating number of Months between two dates by Year
    By ajalfonso in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-23-2017, 03:39 PM
  3. [SOLVED] Calculate number of months for a specific year between 2 cells with dates in them
    By accounting2015 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-25-2015, 12:22 AM
  4. How to calculate number of months by date range for a specific year?
    By redstyles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2015, 05:53 PM
  5. Dates in Excel - Counting the Number of Months in a Year
    By uclaboy82 in forum Excel General
    Replies: 1
    Last Post: 06-04-2012, 11:13 PM
  6. Replies: 3
    Last Post: 03-12-2009, 09:54 AM
  7. Replies: 5
    Last Post: 06-07-2008, 02:32 PM

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