Find number of weeks in fiscal year

[SaraWitch]

Hello peeps,

I'm trying to calculate the number of weeks in a fiscal year. I've tried several formulas where I count the Sundays, Mondays, etc., but so far none are working. I need something really simple so users can just put in 01/04/[relevant year] and 31/03/[following year].

The fiscal year runs from the first Monday in April until the first Sunday after 31st March the following year. I have a formula that calculates these dates based on the entry dates (e.g., =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+6) for the Mondays and =IF(MOD(B1-1,7)>7,B1+7-MOD(B1-1,7)+7,B1+7-MOD(B1-1,7)) for the Sundays.

I then found a formula which is based on the difference in days from 1st January to fiscal year start (=WEEKNUM((A1-[number of days difference]),1)) but can't find a way of finding a formula to change date to 1st January in the same year as the date entered in another cell (fiscal year start date), to then calculate difference - this all needs to be done automatically from just two dates entered by user.

I'm starting to tie myself up in knots, so any practical advice on how to find the number of weeks in a fiscal year based on the above criteria (first April Monday, first Sunday after 31st March) would be greatly welcomed!

 

[Dave Patton]

T202211a.xlsm
	A	B	C	D	E
1			Start	End	Weeks
2		31-Mar-22	Mon 04-Apr-22	Sun 02-Apr-23	52.000
3		31-Mar-23	Mon 03-Apr-23	Sun 07-Apr-24	53.000
4			1-Jan-22	31-Dec-22	52.143
5			1-Jan-24	31-Dec-24	52.286
6
9b

Cell Formulas
Range	Formula
C2:C3	C2	=WORKDAY.INTL(B2,1,"0111111")
D2:D3	D2	=WORKDAY.INTL(EDATE(B2,12),1,"1111110")
E2:E5	E2	=(D2-C2+1)/7

or

T202211a.xlsm
	A	B	C
1
2		1-Apr-22	52
3		1-Apr-23	53
4
9b

Cell Formulas
Range	Formula
C2:C3	C2	=(WORKDAY.INTL(EDATE(B2-1,12),1,"1111110")-WORKDAY.INTL(B2-1,1,"0111111")+1)/7

 

[SaraWitch]

WOW. Just WOW. Both work perfectly, and second even better for my purposes as it references just one other cell.

This is brilliant - thank you, @Dave Patton!

 

[Dave Patton]

Thanks for the feedback.