Forum Discussion
Formula for financial year, 13 periods & 52/53 weeks
Hi,
I was hoping someone could help.
I inherited a spreadsheet at work that tracks document reviews and the times it has taken to do them, as part of this it tracks the week and period in the year these were closed this is used for internal KPI purposes.
The industry I work in splits the years into 13 periods consisting of four weeks each, each year starts on the first Monday in April. There are currently some formulas in the background of the spreadsheet (FY Data), that do all the calculations for working out the periods and weeks., this, however, doesn't consider those years that consist of 53 weeks, and this throws the calculations off when this occurs.
I was hoping someone could help me simplify these formulas and help edited them to consider the issue with the 53-week years, I believe the next one is due in 2024.
The main sheet WA Tracker is the main sheet used, the KPI's are calculated from columns AQ, FY Data has the current formulas, and the sheet named New I added just to show how the current year is broken down into periods and weeks.
Hope this makes sense but feel free to ask for some more info if required.
Billy
9 Replies
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.
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!
