Determine week in fiscal year in Excel

I am trying to get the 1st-53rd week of the fiscal year for the dataset ranging from 2/1/14-1/31/15. I am putting the first day of the fiscal year as a separate week.

The =WEEKNUM() functions gives the week of the year for a calendar year, but creates overlaps for the fiscal year. For example, in a normal calendar week five would be labeled on days 2/1/2014 and 1/31/15.

Example of dataset:

Transaction_Date
2/1/14
2/2/14
3/5/14
10/2/14
1/1/15

Desired result:

Transaction_Date    Week_Of_Fiscal_Year
2/1/14               1
2/2/14               2
3/5/14               6
10/2/14             36
1/1/15              49
1/31/15             53

Solution 1:

My approach is to "shift" fiscal year to the beginning of the calendar year, so weeknum calculate the correct result:
=WEEKNUM(A2-31,10+WEEKDAY(DATE(YEAR(A2-31),1,2),2)), where:

• A2-31 is the "shifted" date: move everything 31 days before so 1st of February will be 1st of January
• WEEKDAY(DATE(YEAR(A2-31),1,2),..) this is to get the first day of week in the current fiscal year (as 1st of February is the first week and second week starts at second of February, therefore first day of week is also the day after the shifted date (2nd of January)
  • 10+WEEKDAY(...,2))
  • second argument of WEEKNUM is the first day of the week, which is 11 for Monday, 12 for Tuesday ...
  • second argument for WEEKDAY is the starting day of the week, need to use 2 for MONDAY
  • 10+ to convert from 1-7 to 11-17

This formula works for each year, start of fiscal year at 1st of February and start of second week at 2nd are "hard coded" in the formula.

Solution 2:

I started working with WEEKNUM translations, dealing with calendar year transition, etc., then realized there is another approach for this that is simple and straighforward:

Column A is your dates. Column B is your week of the fiscal year. The first day of the FY (Feb 1) is entered in A2 as a reference date, and it will always be in week 1, so that can be hard coded in B2.

The formula for subsequent dates can be copied down from B3, which would be:

=CEILING((WEEKDAY(A$2)+A3-A$2)/7,1)

Explanation

This just relies on the difference between the transaction date and day 1 of the FY, and the fact that weeks contain 7 days. The WEEKDAY function factors in what day of the week the FY starts on, and the date difference and CEILING function handle fractional week differences due to the day of the week the transaction falls on.

It isn't necessary to include error checking for dates beyond January 31 of the next calendar year because you can control the transaction date inputs.

______

Solution without reference date

If you don't want to require entering the first day of the FY, you can derive it from the transaction date. In B3, for example, instead of referencing cell A$2, you could use:

DATE(YEAR(A3)-IF(MONTH(A3)=1,1,0),2,1)

This determines the FY from the transaction date and converts that to February 1 of that year. The simple formula in the original solution would become:

=CEILING((WEEKDAY(DATE(YEAR(A3)-IF(MONTH(A3)=1,1,0),2,1))+A3-DATE(YEAR(A3)-IF(MONTH(A3)=1,1,0),2,1))/7,1)

Solution 3:

I figured out a much simpler solution for calculating the week number based on a fiscal year 4 4 5 schedule.

=ISOWEEKNUM(TODAY()-301)

-301 represents the adjustment to the fiscal calendar as if your calendar started in January. In our case the fiscal year starts at the end of October normally hence a larger adjustment to the date. So depending on where your year starts and if you lag a year or not you will need to add or subtract to get it working correctly. Either way, it's simple math.

Obviously if you want to calculate on a date value within a cell then simply change TODAY() to your cell reference. =ISOWEEKNUM(A1-301)