VBA script for having a fiscal year and finding the months between the start and end date

[dandanwrld]

I need help figuring a vba script that has a fiscal year from November 1 to October 31 the next year. I need to find the amount of months within the fiscal year from the start date and end date. For example. If the start date is April 15 2021 and the end date is April 15 2023, then the amount of months would be 6.5 because it is 6.5 months from the end date and then 5.5 months from start date.

Sorry if my wording is confusing.

 

[OaklandJim]

If I understand what you need you can use worksheet formulas to do it. Here is a portion of a worksheet.

MonthsUntilOrAfter.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Fiscal Year
2	Start	End
3	11/1/2022	10/31/2023
4
5	Start Date	4/15/2021	18.56	Months until 11/1/2022
6
7	End Date	4/15/2023	5.42	Months after 10/31/2023
8			6.54	Months until 10/31/2023
9		Months	11.96
10
11	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Average days
12	31	28.25	31	30	31	30	31	31	30	31	30	31	30.4375
Sheet1

Cell Formulas
Range	Formula
E5	E5	=(C3-D5)/30.434
F5	F5	="Months until " & TEXT(C3, "m/d/yyyy")
F7	F7	="Months after " & TEXT(D3, "m/d/yyyy")
F8	F8	="Months until " & TEXT(D3, "m/d/yyyy")
E7	E7	=(D7-C3)/30.434
E8	E8	=(D3-D7)/30.44
E9	E9	=SUM(E7:E8)
O12	O12	=AVERAGE(C12:N12)

 

[OaklandJim]

The previous post had an error. Here is a correct one.

MonthsUntilOrAfter.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Fiscal Year
2	Start	End
3	11/1/2022	10/31/2023
4
5	Start Date	4/15/2021	18.56	Months until 11/1/2022
6
7	End Date	4/15/2023	5.42	Months after 11/1/2022
8			6.54	Months until 10/31/2023
9		Months	11.96
10													Average days
11	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	in a month
12	31	28.25	31	30	31	30	31	31	30	31	30	31	30.4375
Sheet1

Cell Formulas
Range	Formula
E5	E5	=(C3-D5)/30.434
F5	F5	="Months until " & TEXT(C3, "m/d/yyyy")
F7	F7	="Months after " & TEXT(C3, "m/d/yyyy")
F8	F8	="Months until " & TEXT(D3, "m/d/yyyy")
E7	E7	=(D7-C3)/30.434
E8	E8	=(D3-D7)/30.44
E9	E9	=SUM(E7:E8)
O12	O12	=AVERAGE(C12:N12)