Using the WEEKNUM function in Excel to calculate week numbers on the basis of 7 day blocks, primarily for Australian Financial Years

Question

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.

Answer 1

You can calculate the day and week since a specific start date as the difference between the current date and the start date you specify.

I created this:

At the top, I have two named cells:

1. cal_year_start_month
2. fin_year_start_month

The date column just contains data.

'year of date' formula:

=YEAR([@date])

'first date of calendar year' formula:

=DATE([@[year of date]],cal_year_start_month,1)

'day of year' formula is the difference between 'date' and 'first date of calendar year':

=DAYS([@date],[@[first date of calendar year]])+1

'week of year' is:

=ROUNDUP([@[day of year]]/7,0)

'first day of financial year' is:

=DATE(IF(MONTH([@date])<fin_year_start_month,[@[year of date]]-1,[@[year of date]]),fin_year_start_month,1)

'day of financial year' is:

=DAYS([@date],[@[first day of financial year]])+1

'week of financial year' is:

=ROUNDUP([@[day of financial year]]/7,0)

The 'week 1' column is just there so I could filter the data to paste the screenshot above:

=OR([@[week of year]]=1,[@[week of financial year]]=1)

If you wanted to, you could combine them into...

'week of calendar year':

=ROUNDUP((DAYS([@date],DATE(YEAR([@date]),cal_year_start_month,1))+1)/7,0)

'week of financial year':

=ROUNDUP((DAYS([@date],DATE(IF(MONTH([@date])<fin_year_start_month,YEAR([@date])-1,YEAR([@date])),fin_year_start_month,1))+1)/7,0)