The formula requires adjustment to reflect FY April - March

[hananak]

Dear all,

I am working on a Amortization schedule that is based on year that runs from Jan - Dec. But our Financial year runs from April - March. Please see the below two formulas. I am not sure how to adjust these to reflect this change.

Accounting Yard Prepayment Schedule - Copy.xlsx
	A	B	C	D	E	F	G
4	Year	2022
5	Current Month	1
6	Current Date	Jan-22	I want it to show April 22 instead of Jan-22
Control

Cell Formulas
Range	Formula
B6	B6	=DATE(Control!$B$4,Control!$B$5,1)

Cells with Data Validation
Cell	Allow	Criteria
B4	List	2018,2019,2020,2021,2022,2023,2024,2025
B5	List	1,2,3,4,5,6,7,8,9,10,11,12

Secondly,

Accounting Yard Prepayment Schedule - Copy.xlsx
	N	O	P	Q	R	S	T	U	V	W	X	Y
3	This is what I want - highlighted in green
4	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23
5	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
6	-	-	-	750	750	750	750	750	750	750	750	750
Prepayment

Cell Formulas
Range	Formula
N5:Y5	N5	=DATE(Control!$B$4,COLUMNS($N$5:N5),1)
N6:Y6	N6	=IF(AND(OR($B6=N$5,N$5>$B6),OR($C6=N$5,N$5<$C6)),IFERROR(IF(AND(OR(YEAR($B6)=YEAR(N$5),YEAR($B6)<YEAR(N$5),YEAR(N$5)>YEAR($B6)),OR(MONTH($B6)=MONTH(N$5),MONTH($B6)>MONTH(N$5),MONTH(N$5)>MONTH($B6))),SUM($H6:$I6)/$F6),0),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N5:Y8	Expression	=MOD(COLUMN(),2)=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
N6:Y6	Custom	=AND(ISFORMULA(N6))

Your help would be really appreciated.
Thanks.

 

[hananak]

Can anyone help me in adjusting the formula in B6 please?

 

[Fluff]

How about

=DATE($B$4,$B$5+3,1)

You should never refer to the name of the sheet the formula is on.

 

[hananak]

How about

=DATE($B$4,$B$5+3,1)

You should never refer to the name of the sheet the formula is on.

Thanks for your reply. It's working.

Can you help in adjusting the formula in N5 cell. so instead of Jan, I should have Apr?

 

[Fluff]

Just add +3 to columns

 

[hananak]

Just add +3 to columns

Amazing! Now the schedule is working as per my requirements.

Thank you for your quick reply and providing the solution.

 

[Fluff]

You're welcome & thanks for the feedback.