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

Bootharoo

New Member
Joined
Nov 5, 2018
Messages
2
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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Welcome to the Forum!

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


Book1
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
Sheet1
 
Last edited:
Upvote 0
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

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

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top