Issue amending Calender dates within Excel, from Calendar year to financial year

Hi,

Having moved companies, I am looking to use a standard template used previously to record Annual Leave for staff. Unfortunately, the template is set up to run from a calendar year (Jan - Dec) and the annual leave at the new company is from Apr - March. Having tried a couple options I cannot amend to reflect the year April - March. I think the issues lies on the first tab within calendar with this formula - =IFERROR(IF(TEXT(DATE(Calendar_Year,ROW($A1),1),"ddd")=LEFT(C$5,3),DATE(Calendar_Year,ROW($A1),1),""),""). As it set up as a table rather than calendar, it's not as easy as changing the presets.

I've attached the sheet herewith. Any help would be much appreciated.

The problem is with the use of row() to find the month in the formula.

I have managed to change the formula's so that it will show the months properly
Only thing I got stuck on is the conditional formatting rules as the line for july is now showing ### after 31 july. while in the original file it shows 1 for august 1. I was not able to fix that.
But that might be easier for you to understand what goes wrong there..

I think I got far enough to get you well on your way to a full solution, 95% is done I think.

Excellent, that's broken the back of it for me! I think the devisor in the formula should be 8 rather than 7 to encompass the months where the month begins on Saturday, but other than that, it's sorted.

Thank you very much for your help!

Glad I could help. my approach was to try and get 1 formula for all 40 columns but that was too hard.
so you might even consider simplifing the formula even more by just put hard value 1 thru 7 instead of the mod-formula I created..