Results 1 to 3 of 3

Thread: Month counting formula between 2 dates, including months per fiscal year
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Month counting formula between 2 dates, including months per fiscal year

    Hi,

    We take on contracts typically ranging from 3 months to 36 months. we always use the 1st of the month for the start and end dates.

    for example:

    Contract 1: 1/4/18-1/12/18 = duration 8 months
    Contract 2: 1/2/18 - 1/2/18 = duration 12 months
    Contract 3: 1/1/18 - 1/1/20 = duration 24 months

    I have put the DATEIF formula in place to count the number of months between 2 dates.

    The hard bit is counting the number of months in each financial year. Our year end is 1st April for the benefit of the spreadsheet. So in the context of the above contracts:

    Contract 1: all 8 months are in Fiscal year ending 1/4/2019
    Contract 2: 2 months are in Fiscal year ending 1/4/2018 and the remaining 10 months are in Fiscal year ending 1/4/2019
    Contract 3: 3 months are in fiscal year ending 1/4/2018, 12 months are in fiscal year ending 1/4/2019 and the remaining 9 months are in fiscal year ending 1/4/2020

    If anyone can provide me with a decent formula for the above I would appreciate it

    Thanks

    Joe!

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,468
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Month counting formula between 2 dates, including months per fiscal year

    Welcome to the Forum!

    D3: =IFERROR(DATEDIF(MAX($B3,EDATE(D$2,-12)),MIN($C3,D$2),"m"),0)

    ABCDEFG
    1Year end------>
    2Contract #StartEnd1 Apr 20181 Apr 20191 Apr 20201 Apr 2021
    311 Apr 20181 Dec 20180800
    421 Feb 20181 Feb 201921000
    531 Jan 20181 Jan 202031290



    Last edited by StephenCrump; Nov 5th, 2018 at 10:56 PM.

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Month counting formula between 2 dates, including months per fiscal year

    Quote Originally Posted by StephenCrump View Post
    Welcome to the Forum!

    D3: =IFERROR(DATEDIF(MAX($B3,EDATE(D$2,-12)),MIN($C3,D$2),"m"),0)

    A B C D E F G
    1 Year end ------>
    2 Contract # Start End 1 Apr 2018 1 Apr 2019 1 Apr 2020 1 Apr 2021
    3 1 1 Apr 2018 1 Dec 2018 0 8 0 0
    4 2 1 Feb 2018 1 Feb 2019 2 10 0 0
    5 3 1 Jan 2018 1 Jan 2020 3 12 9 0

    ABSOLUTE LEGEND!! I can't tell you how much time this will save me! Thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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