Excel Week Number for Financial year was a question that one of my students asked me recently on a training course and I couldn’t really find a definitive answer.
So, I decided to see if I could find an answer myself. I will admit that in looking for a formula to determine which week it was in the financial year I did create a monster of a formula that worked, but looked like it was the calculation for re-entry of the space shuttle. I need to create something a little simpler.
By the way, check out this financial year formula webpage should you want to see more about financial year calculations.
The steps to Calculating the Week Number for the Financial Year
There can be two solutions to this problem. The answer depends on how you view the financial year. Is it starting with the first Monday in the week? Or do you want the first day of the week to start on the first of April irrespective of what day of the week that falls on.
Calculated the Week Number for the Financial Year Starting on April 1st
Calculating the Day of the Year
If you want to break down the year into financial periods of week numbers with 7 days starting from 1st of April, you will first need to work out what day it is in the financial year. Now to calculate the day of the year is pretty straight forward and can be accomplished using the following formula:
As you can see the formula for calculating the day of the year is:
=A2-DATE(YEAR(A2),1,1)+1
Basically, you take the date which in the above example is cell A2 then subtract the date of the 1st of January for the current year. This is done by using the DATE() function in combination with the YEAR function.
Finally, we add 1 to the value so that there is no day 0. (If we fail to add a one to the formula then the 1st of January will appear as day zero).
Calculating the Financial Day of the Year
Now that you know how to calculate the day of the year you now need to adjust the formula to calculate the financial day of the year. This is solved by using an IF function to determine which financial year we are in. The first thing to do is to ascertain which financial year you’re in. The can be done as follows:
=IF(MONTH(A2)<=3,
So in the above formula the if function is checked to see whether the month is Jan – Mar. If so then the formula will be:
=IF(MONTH(A2)<=3,A2-DATE(YEAR(A2)-1,4,1)+1,
Basically we are taking the current date in cell A2 and then subtracting all the days from the beginning of April in the previous year. Now to add the final part of the financial day formula.
=IF(MONTH(A2)<=3,
A2-DATE(YEAR(A2)-1,4,1)+1,
A2-DATE(YEAR(A2),4,1)+1
)
Finally, in the false part of the IF function, we would take all the days from April of the current year. Notice that we also add a plus 1 at the end so that there are no day 0’s. The result is:
Note that the formula also takes into account leap years. The next I believe will be in 2024.
Excel Week Number Financial Year
The Excel Week Number Financial year formula is why you’re here on this blog. You might have already been able to work it out from following along. But if you need a little extra help then don’t worry here goes:
=ROUNDUP(
IF(MONTH(A2)<=3,
A2-DATE(YEAR(A2)-1,4,1)+1,
A2-DATE(YEAR(A2),4,1)+1
)/7,0)
That’s It! Basically, I’ve encased the IF function in a ROUNDUP function divided it by 7 and rounded the result to 2 decimal places.
From the above picture you can also see that the Excel week number financial year formula takes into account leap years by adding an extra day in Week 53.
So, if you are wanting to break down the year into 7 days starting from 1st April then what you’ve just followed from above will suit you just fine.
But what if you want to have your week numbers begin on the first day of the week. Say the week begins on the first Monday in the year? What do you do then?
Excel Financial Week Formula for First Full Week
Ok you want to break down your figures by the week number for the financial year. But you want to control the week number. So the first week will be wherever the 1st April exists.
The formula for the week number with Monday as the first day of the week will look like:
=WEEKNUM(A2-90,2)
Nice and simple eh. So if is the formula you are looking for then all you need to do is place it in a spreadsheet that looks like the following:
As you can see from the above the first week begins on April the 1st.
However, there is a problem should you want the weekday to begin on a Monday. That’s because if we minus 90 days to the current date there’s no guarantee the date will be a Monday. Have a look at the following to understand what I’m talking about:
As you can see from above, when I subtract 90 days from 1st of April (Beginning of the financial year) I arrive at 1st Jan and that’s perfect for the week numbers.
However, when I use the =TEXT(A2,”ddd”) formula to find out what day of the week that day is on then it appears as a Friday for the financial year when actual date is a thursday.
Now this might not make any difference because you’re really interested in Week Numbers for the financial year. So you might not be interested in the days of the week. Nevertheless, it would be good to ensure that the first day of the week is a Monday. For that I would change which day of the week I would like the first day of the week to be a Tuesday. As the following screenshot shows:
Now for my records I can be sure that the financial year, broken down by week number, will be from Monday to Sunday. Of course you need to remember that the date the week number function is using is not really a Monday. But that’s fine so long as you get the day of the week from the original date.
As you can also see from below this works for leap years, the next I believe is in 2024.
Excel Formula for Financial Week Number
So there you have it. As you can see, calculating the financial week number all depends on the method, or result that you want to see. Is it broken down into 7 day periods starting from the 1st of April? Or do you want to match up the date with the days of the week.
Please let us know below if you have any comments. Perhaps you’ve felt that I’ve totally missed the point here and if so then let me know what you do to create week numbers for the financial year. It would be great as well if you could include the leap year within your calculation.
Want to find out more about other Excel formulas then check out this financial formula page.
Or check out this how to quickly remove blank rows in Excel tutorial. Yes it really is that fast.
Embark on a transformative journey with our Excel 365 beginners’ course, designed for those who value efficiency in today’s fast-paced world. This dynamic, 3-hour half-day session is more than just a basic training; it’s an in-depth exploration into the intricacies of Excel 365. Inspired by real-world challenges, such as calculating the week number for a financial year, our course demystifies complex Excel tasks. We turn daunting, ‘space shuttle re-entry’ level formulas into something elegantly simple, ensuring that even the most intricate calculations become accessible to you.
Our commitment extends beyond the classroom with a full month of post-training support, guiding you in applying your new skills effectively. Whether you aim to master financial formulas or enhance your overall Excel proficiency, our course equips you with the necessary tools. Tailored for those with tight schedules, this half-day course is the perfect opportunity to make significant progress in Excel without the need for a lengthy commitment. Join us and transform your approach to Excel, turning complex challenges into straightforward, manageable tasks.