How to calculate months in fiscal year

[maria_v]

Hello fellow colleagues,
I have a task to calculate the number of months in a financial year (Australia) for grant payments that start in one year and end in the next, or in a few years. E.g. a $49,790.40 grant, which payments begins on the 16/10/2018 and ends on the 30/06/2019. The time span of the grant payment is 8 months (a difference of Start and End Dates). The next step is to find the # of months per financial year (e.g. 2018/2019 m). I am not sure how to go with it as excel tutorials only use one timepoint and not two, as I have it (Start date and End date). How would I do it? (In Australia, financial year begins on the 1st July and ends the next year on the 30th June).

Grant value ($ AUD)	Grant purpose	Start Date	End Date	months(general)	$per month
$49,790.40​	Agriculture	16/10/2018​	30/06/2019​	8​	6223.8​
$49,790.40​	Agriculture	16/10/2018​	30/06/2019​	8​	6223.8​
$100,000.00​	Disaster Relief	10/12/2018​	7/12/2019​	11​	9090.909091​
$15,500.00​	Infrastructure/Development	8/05/2018​	20/06/2018​	1​	15500​

Thank you for your help,
Kind regards,
Maria

 

[Peter_SSs]

Welcome to the MrExcel board!

In your first example, the period includes at least parts of Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun
That is 9 months, but you are only counting 8. Which one are you not counting.

More so with an example like your last one. If instead those dates were 8/06/18 to 20/07/18 it is of course critical which month is counted/not counted as the two involved are in different financial years.

 

[maria_v]

Welcome to the MrExcel board!

In your first example, the period includes at least parts of Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun
That is 9 months, but you are only counting 8. Which one are you not counting.

More so with an example like your last one. If instead those dates were 8/06/18 to 20/07/18 it is of course critical which month is counted/not counted as the two involved are in different financial years.

Hi Peter,
For the months part I used =DATEDIF(C2,D2,"M") formula to calculate the months (whole months?) in that period. I suspect it is 8 instead of 9 due to one month not being whole. However, I am still unsure how to calculate the months in the fiscal year from two dates.

Cheers,
Maria

 

[Peter_SSs]

See if this could work for you.
H1:L2 are helper rows to keep the main formulas a bit shorter.
H1 contains the data 1 July 2016. Other cells in those top rows contain formulas as shown

22 07 09.xlsm
	C	D			G	H	I	J	K	L
1						2016	2017	2018	2019	2020
2						2017	2018	2019	2020	2021
3	Start Date	End Date				2016-17	2017-18	2018-19	2019-20	2020-21
4	16/10/2018	30/06/2019						9
5	16/10/2018	30/06/2019						9
6	10/12/2018	7/12/2019						7	6
7	8/05/2018	20/06/2018					2
8	29/06/2018	1/07/2020					1	12	12	1
FY

Cell Formulas
Range	Formula
I1:L2	I1	=EDATE(H1,12)
H2	H2	=EDATE(H1,12)-1
H3:L3	H3	=YEAR(H1)&"-"&RIGHT(YEAR(H2),2)
H4:L8	H4	=IF(OR($D4<H$1,$C4>H$2),"",MONTH(MIN(EDATE(H$2,6),EDATE($D4,6)))-MONTH(MAX(EDATE(H$1,6),EDATE($C4,6)))+1)