+ Reply to Thread
Results 1 to 4 of 4

Calculate number of months in a specific year between two dates.

  1. #1
    Registered User
    Join Date
    09-09-2019
    Location
    London, England
    MS-Off Ver
    2017
    Posts
    2

    Calculate number of months in a specific year between two dates.

    Problem:
    In Col. A I have ContractID, Col B a contract start date and in Col. C I have a contract end date.
    I want to output the number of months that occur in the specific years 2017, 2018, 2019, 2020,2021 and 2022 in each contract.

    Example input (space delimited):


    ContractID StartDate EndDate
    AAA 01.04.2019 31.12.2033
    BBB 25.09.2020 10.05.2033
    CCC 06.06.2017 05.05.2020
    DDD 01.04.2019 07.04.2022

    Desired output (in Cols)(space delimited):

    Yr(2017) Yr(2018) Yr(2019) Yr(2020) Yr(2021) Yr(2022)
    0 0 8 12 12 12
    0 0 0 3 12 12
    6 12 12 5 0 0
    0 0 4 12 12 4

    Any suggestions?

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calculate number of months in a specific year between two dates.

    Would you clarify how you calculate the number of months please

    Take AAA for instance. In 2019 there are 9 month from 1/4/2019 to 31/12/2019 (including the start month). You show 8.
    BBB. In 2020 there are 4 months from 25/9/2020 to 31/12/2020 (including the start month). You show 3
    CCC. In 2017 there are 7 months from 6/6/2017 to 31/12/2017 (including the start month). You show 6
    DDD. In 2019 there are 9 months from 1/4/2019 to 31/12/2019(including the start month). You show 4
    CCC In 2020 there are 4 complete months from 1/1/2020 to 5/5/2020. You show 5


    Apart from DDD do we exclude the start month even if it's the first of the month as in AAA and DDD? And do we INCLUDE the last month if the end date is only partly into the month as in the CCC 2020
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calculate number of months in a specific year between two dates.

    Hi

    Notwithstanding the earlier questions perhaps the attache helps
    With 1st January year dates on row 1 starting in column D copied across and Start/End dates in B2:Cnn then
    D2:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    copied across and down.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Calculate number of months in a specific year between two dates.

    Please find my solution for this problem.
    Attached Files Attached Files

+ 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. Formula for number of months for specific year between two dates
    By KalaiP in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-01-2018, 01:05 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. Replies: 4
    Last Post: 11-28-2011, 06:21 AM
  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