Week number based on the year starting with a particular date

[UmairKamal]

Dear users,

I am using MS Excel 2016 and have a dataset of nearly 650,000 rows. The financial year runs from April to March. however for this dataset, the report requires a weekly analysis. the week commences on Monday. so for example, the year 2023-24 starts with Monday, 27 March 2023 and ends at 31 March 2024 (i.e. 52 weeks).

I was using the formula

=WEEKNUM(C2-(1+DATE(YEAR(C2),3,26)-DATE(YEAR(C2),1,1)))

where the date is in Column C.

This was working fine until the date 1/1/2024.

week 40 finishes on 31/12/2023, however, the date 01/01/2024 is also being considered in week 40, whereas it should be week 41 as 01/01/2024 is a Monday.

please advise.

kind regards.

 

[Jeffrey Mahoney]

Please try this:

Book2
	C	D	E	F	G	H
1	Date	WeekNum			Year	Year Beg Date
2	1/1/2024	44			2022	2/28/2022
3	3/15/2024	3			2023	2/27/2023
4	2/1/2024	49			2024	2/26/2024
5	2/25/2024	52			2025	2/24/2025
6					2026	2/23/2026
7
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=ROUNDUP((C2-VLOOKUP(C2,Table1[Year Beg Date],1,TRUE))/7,0)
H2:H6	H2	=DATE(G2,3,1)+(1-WEEKDAY(DATE(G2,3,1),2))

Named Ranges
Name	Refers To	Cells
TBD_Tbl	=Sheet1!$G$2	H2

 

[Jeffrey Mahoney]

Sorry about the conversion from dd/mm/yyyy to US mm/dd/yyyy. You can switch that around with no issues.