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/15Desired 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 537 Answers
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-31is the "shifted" date: move everything 31 days before so 1st of February will be 1st of JanuaryWEEKDAY(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
WEEKNUMis the first day of the week, which is 11 for Monday, 12 for Tuesday ... - second argument for
WEEKDAYis the starting day of the week, need to use2for 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.
4I 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)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)
I have a much much simplier solution:
--> My fiscal year begins the fist week of August, Sunday August 4th 2019.
Step 1 -- I calculated the difference between January 1st and August 4th 2019. This can easily be done using Excel. It came out to 215 days.
Step 2 -- =WEEKNUM((A2-215),1)
note: do not use the dollar sign if you'll be pulling the formula down through the entire sheet
Cell A2 had the date of 11/8/2019, and the formula returned the FY week #15. I checked throughout the range and I successfully had weeks 1 though 53 assigned based on my FY calendar.
All the formula is doing is shifting your "January 1st" to your first Fiscal year day. So, if you have a fiscal year that begins on a different day than mine then just recalculate the shifted day amount by finding the difference from January 1st.
Hi I struggled with this and I ended up using the below formula
=INT((A6-DATE(YEAR(NOW()),7,-7))/7)
As it is the Fiscal year you can just change the starting month to 7
The best solution for me was:
=IF(((WEEKNUM(D2)-WEEKNUM(DATE(2020,7,1)))+1)<0, (WEEKNUM(DATE(2020,7,1))+WEEKNUM(D2))-1, (WEEKNUM(D2)-WEEKNUM(DATE(2020,7,1)))+1)for the Fiscal year starting 01/July/2020 and cell D2 contains the date. The other formulas fail to acknowledge weeks as 7 day periods starting on Sunday. If you also have data with dates occurring between the end of December and beginning of January, this formula won't split a week containing two years into two separate weeks. For example week starting 27/12/2020 to 2/1/2020 will be week 27, not split into weeks 27 and 28. .
Having the first day of fiscal year hardcoded in the formula was good enough for me but shouldn't be too hard to add a fiscal year column and change the reference to that if you're dealing with multiple years worth of data.
=(WEEKNUM(C2)-13)
Get the value of the date in Weeknum then subtract the previous weeks in my case it was 13 weeks, then right-click the cell and select format cells in the menu select it as numbers without decimal. You will get the week for the fiscal year.
