TLDR
Is there a way to use the WEEKNUM
function in Excel to simply calculate week numbers on the basis of 7 day blocks without any regard whatsoever to what weekday the first date falls on?
Context
I'm working on some very large data files in Excel (each containing over 800,000 rows of data).
Two of the columns in each of these files are designed to calculate week numbers (one calculates the week number based on Calendar Year, the other does so based on Financial Year). In this case a Financial Year runs from 1 July - 30 June.
In both cases, the value for 'year' is based on the same Form_Finalised_Date
column, and weeks are calculated on the basis of seven actual days (i.e. it is totally irrelevant what weekday the 1st January falls on (in the case of Calendar Years) or 1st July falls on (in the case of Financial Years).
By way of explanation, for Calendar Years this means:
- 1st - 7th January will always be Week 1
- 8th - 14th January will always be Week 2
- 15th - 21st January will always be Week 3
- and so on.
And, in the case of Financial Years:
- 1st - 7th July will always be Week 1
- 8th - 14th July will always be Week 2
- 15th - 21st July will always be Week 3
- and so on.
My current formula for Calendar Year - it's accurate, but...
In terms of a formula for calculating the Calendar Year week number, I've had to settle for the following:
=IF([@[Calendar Year]]=2015,WEEKNUM([@[Form_Finalised_Date]],14),
IF([@[Calendar Year]]=2016,WEEKNUM([@[Form_Finalised_Date]],15),
IF([@[Calendar Year]]=2017,WEEKNUM([@[Form_Finalised_Date]],17),
IF([@[Calendar Year]]=2018,WEEKNUM([@[Form_Finalised_Date]],11),
IF([@[Calendar Year]]=2019,WEEKNUM([@[Form_Finalised_Date]],12),
IF([@[Calendar Year]]=2020,WEEKNUM([@[Form_Finalised_Date]],13),
IF([@[Calendar Year]]=2021,WEEKNUM([@[Form_Finalised_Date]],15),
IF([@[Calendar Year]]=2022,WEEKNUM([@[Form_Finalised_Date]],16),
IF([@[Calendar Year]]=2023,WEEKNUM([@[Form_Finalised_Date]],17),
IF([@[Calendar Year]]=2024,WEEKNUM([@[Form_Finalised_Date]],11),
IF([@[Calendar Year]]=2025,WEEKNUM([@[Form_Finalised_Date]],13),
IF([@[Calendar Year]]=2026,WEEKNUM([@[Form_Finalised_Date]],14),
IF([@[Calendar Year]]=2027,WEEKNUM([@[Form_Finalised_Date]],15),
IF([@[Calendar Year]]=2028,WEEKNUM([@[Form_Finalised_Date]],16),
"N/A"))))))))))))))
As you can see, to get the desired result for Calendar Year weeks I've had to determine what weekday 1st January falls on for each of the years between 2015 to 2028 so I can add the corresponding Return_type argument for the year.
However, while this works, it:
- can't be adapted to calculate Financial Year week numbers (at least I haven't found a way to do it)
- seems unnecessarily long as I've had to write it to allow for the next eight years to try and future proof it!
Instead, I was hoping to have a much simpler formula that would just look at the date from the Form_Finalised_Date
column and work out the week number by effectively starting on 1 January and counting each 7 days as a week and so on. This way there would be no need for manually adding more IF
functions as more years appear in the data source.
My current formula for Financial Year - concise, but not 100% accurate
After spending a lot of time on trying to adapt my Calendar Year formula to calculate Financial Year week numbers, I eventually gave up and came up with something else that seems fairly close to what I need. This formula is as follows:
=WEEKNUM([@[Form_Finalised_Date]]-(1 + DATE(YEAR([@[Form_Finalised_Date]]),6,30)-DATE(YEAR([@[Form_Finalised_Date]]),1,1)))
However, the formula doesn't always calculate the correct result (more details in screenshot below).
In the above example:
- Column C shows the results of my very long formula above for calculating the Calendar Year week number. The results are accurate, but since I'm using the
IF
function to specify the calculation depending on the year, it's really not ideal as it means we need to manually change the formula whenever we get data for a different year. I also can't seem to adapt it for calculating Financial Year weeks. - Column E shows the desired result of calculating the Financial Year week number (remembering that a Financial Year runs from 1st July to 30th June).
- Column F shows the results of my present formula for calculating the Financial Year week number, but it isn't always producing the correct result (see red circles showing incorrect calculations).
QUESTION
In short, is there a way to use the WEEKNUM function in Excel to just simply calculate week numbers on the basis of 7 day blocks of time. In my specific scenario I need to be able to do this both from 1 January for Calendar Years, and from 1 July for Financial Years.
NOTES:
- The desired results are that the week numbers are calculated on 7 day blocks of time without any regard to the weekday that the 1st of January or the 1st of July falls on.
- Since my formula for Calendar Year works (albeit a little clumsily), I'm mostly concerned with getting a formula that accurately calculates the Financial Year week number.
05/01/20
falls in18th week of the year 2020
,, but in1st week of month May 20
,, now what I've realized that U wanna to find week number for one calendar year starts from01/01/2020
to12/31/2020
or may be07/01/2020
06/30/2021
,, write , if yes confirm through comments.