Forum Discussion
Formula for financial year, 13 periods & 52/53 weeks
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.
mathetes - It's been a while, but I have been playing about with the formula you sent me through and trying to get it to work for my needs. I think I more or less got where I wanted to be, but now I'm receiving the Calculating (8 threads on the status bar, which takes ages to load or just completely crashes. any ideas why this will be happening? it also seems to use up a lot of memory and CPU usage whilst doing this.
is this a processing issue or are the formulas a bit too much?
I've attached an update for you to look at
For me, your last used cell is BH1048566 and your formulas under the yellow headings reference entire columns. Each of your calculations runs through a million or so cells. Since you have a table, why not use structured references to limit the calculation and correct the used range.
Out of interest, an alternative formula for the first instance of a workday following any given date.
= LAMBDA(date,[weekday], CEILING(date - weekday, 7) + weekday )As it stands, the formula treats 1 to be a Sunday and defaults to Saturday. The following would use 1 as a Monday and default to Monday if the weekday is not specified
= LAMBDA(date,[weekday], LET( wd, IF(ISOMITTED(weekday), 2, 1 + weekday), firstInstance, CEILING(date - wd, 7) + wd, firstInstance ) )
Those INDEX formulas referring to entire columns were crippling the workbook. I created dynamic ranges for your columns and swapped out the formula references (There were about 40,000 replacements made with find/replace).
New INDEX looks like this:
=IF(AM5<>"", INDEX(WeekNumber, MATCH(1, (AM5 >= MondayDates) * (AM5 <= SundayDate), 0)), "")I had to shut down my computer! Yes, I suspect you're overworking things in one way or another.
Because things just froze, I was unable to look at the size of the sheets--the file itself is 3 MB in size, and if there are many formulas in that, that could easily lock things up. At the top of the screen, what I could see, was a deeply nested IF formula.....but I wasn't able to even see the whole of it.
Is it possible on one of the occasions when it just takes ages but does load to take measures to decrease the size? Either by cutting down on length of time (How many fiscal years are you incorporating into this?), or eliminating some of the columns? Does everything need to be open to recalculation all the time?
Thanks for this and sorry for only just getting back to you I didn't get the notification you had responded. This is a great help and I will play about to see if I can get it to work for me.
I might come back with some more questions, but really appreciate your feedback
- Just stopped by to give a like!
The 'Leap Week' with the 13 Period financial calendar can be a challenge, not least when you'll have reports doing period over period comparisons transposing dates 28 days, then every 6 years you'll get a 35 day period!
