How can I adjust starting cell of dates to maintain financial year

[jsmythe]

Hi all,

I am trying to update a work template we have for calculating loan repayment schedules as shown in pic.

Cell B21 is linked to cellD12 as the starting month for the contract, and it all follows on from there.
This places June as the last month above the line so we can calculate total interest for the financial year etc, which is the ultimate goal.
However if the starting date is changed to say February, then it flows on consecutively and we have to move the formatting down and the formula boxes etc.

Is there a way to code it so that a june month always sits as the last month on the line, and the dates update around that value, so if it starts in Feb again for example, it will populate from cell B28 onwards, and we have a nice clean worksheet with the financial years lined up nice and easy for us?

Thanks very much, James

[Pepe Le Mokko]

Hi and welcome
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jsmythe]

Hi Pepe,

Have updated attachment, thankyou this is a very helpful way of explaining my question, I will follow this format going forward.
Thanks, JS

[JeteMc]

See if this helps (modeled on the "Original" sheet).
Formula for cell B26: =DATE(YEAR(D8),MONTH(D8),DAY(D6))
Formula for cells B15:B25 =IFERROR(IF(DATE(YEAR(B16),MONTH(B16)-1,DAY(B16))<D$6,"",DATE(YEAR(B16),MONTH(B16)-1,DAY(B16))),"")
Formula for cells A15 and down: =IF(ISNUMBER(B15),SUM(A14,1),"")
Formula for cells C15 and down: =IF(B15=D$6,D$2,IF(F14<=0,0,F14))
Formula for cells D15 and down: =IF(C15>0,D$10,0)
Let us know if you have any questions.

[jsmythe]

Hi JeteMc,

Perfect!!
This does exactly what I was hoping for and is very easy to follow, it is a good way to look at the problem and hopefully help me solve future problems.

Thank you so much for your help!!!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.