Week number in the calendar

[UmairKamal]

Dear users,

I would like to create a calendar in Excel. Here are the basic rules:

1. The financial year runs from April to March
2. The week runs from Monday to Sunday
3. The week numbers are based on the Sundays in that year. for example, week commencing 30 March 2020, Monday ends on a Sunday, 5 April 2020 and should be week 1 for the financial year 2020-21. Similarly, week commencing 22 March 2021, Monday ends on a Sunday, 28 March 2021 and should be week 52 for the financial year 2020-21.
4. Following on from the example above, the week commencing Monday, 29 March 2021 ends on a Sunday 4 April 2021, and should be week 1 for the financial year 2021-22 and so on and so forth.

Considering all my dates are in Column A and I want the week numbers in Column B, please assist me with a formula which automatically calculates the week numbers.

 

[Anonymous1378]

I used a helper column, but if you like walls of text feel free to combine it into one cell

Book3
	A	B	C
1	01-Jan-20	-12.00	40
2	02-Jan-20	-12.00	40
3	03-Jan-20	-12.00	40
4	04-Jan-20	-12.00	40
5	05-Jan-20	-12.00	40
6	06-Jan-20	-11.00	41
7	07-Jan-20	-11.00	41
8	08-Jan-20	-11.00	41
9	09-Jan-20	-11.00	41
10	10-Jan-20	-11.00	41
11	11-Jan-20	-11.00	41
12	12-Jan-20	-11.00	41
Sheet1

Cell Formulas
Range	Formula
B1:B12	B1	=ISOWEEKNUM(A1)-ISOWEEKNUM(DATE(IF(ISOWEEKNUM(A1)=ISOWEEKNUM(DATE(YEAR(A1),4,1)),YEAR(A1),YEAR(A1)-1),4,1))+1
C1:C12	C1	=IF(B1>0,B1,B1+ISOWEEKNUM(DATE(YEAR(A1)-1,12,28)))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$B$1:$C$1096	C1

 

[UmairKamal]

Anonymous1378 Thank you. This works perfectly.