Formula for financial year, 13 periods & 52/53 weeks

billyrichrich83 

Intriguing problem. Given your way of figuring weeks, you're right that 2024 will have 53 Mondays before the first Monday in April of 2025. But that last "week" has 6 days following 3/31/25.

Anyway, I didn't try to follow all of your workbook's various formulas. What I've done here is give you what I think are two easier ways to calculate the first Monday in April for any given year. There are two formulas in the tab labelled "Newer". Each of them requires a fairly new version of Excel, which I'm assuming you have.

The more basic of those formulas, at the top of column H, is this:

=LET(FD,DATE(B2,4,1),FD+CHOOSE(WEEKDAY(FD),1,0,6,5,4,3,2))

where cell B2, highlighted above, is a cell containing a year. The rest of the formula uses

• DATE(B2,4,1) to calculate the first date of the fourth month in the designated year and assign that value to the variable FD
• WEEKDAY(FD) figures out what weekday FD is (Sunday being 1, Monday 2, Tuesday 3, etc)
• CHOOSE based on WEEKDAY result adds a value to FD.
• And the net result is that if FD is a Monday, CHOOSE adds 0, and the whole thing displays that first Monday date; if FD is a Sunday, it adds 1 and displays the result; if it's Tuesday, it adds 6 and displays the date.
• In every case, this formula gives you the date of the first Monday in April of the designated year.

I then used LAMBDA to create, in cell D2, a user-defined function called FstAprMon which simply takes the formula above and allows you to refer to it simply as =FstAprMon(B2)

I've then just extended each of those columns to get the Monday dates for the next 54 or so weeks, and put, adjacent to each column, a week counter. If you play around with years--which I encourage you to do--you'll see that 2024 does end up showing a "53rd week" but 2025 stops after 52, and so on.

Let me encourage you to play around with these formulas in the WA tracker to see if you can use them to achieve the results desired. If not, by all means come back with further questions. 

And if you want to learn about LET and LAMBDA, those highlighted words are now hyperlinks to some good explanations of how to use these new functions.