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

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

3. ## Re: Month counting formula between 2 dates, including months per fiscal year

Originally Posted by StephenCrump
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