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

[billyrichrich83]

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

[#2]

@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.

[#3]

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!

[#4]

@mathetes 

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 

[#5]

@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

[#6]

@billyrichrich83 

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?

[#7]

@billyrichrich83 

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)), "")

[#8]

@billyrichrich83 

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.

[#9]

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
    )
  )

[#10]

Hi All,
Sorry it's been a while been playing about with all the suggestion and finally got it working. Really appreciate all your help with this