Find number of weeks in fiscal year

SaraWitch

Active Member
Joined
Sep 29, 2015
Messages
317
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
T202211a.xlsm
ABCDE
1StartEndWeeks
231-Mar-22Mon 04-Apr-22Sun 02-Apr-2352.000
331-Mar-23Mon 03-Apr-23Sun 07-Apr-2453.000
41-Jan-2231-Dec-2252.143
51-Jan-2431-Dec-2452.286
6
9b
Cell Formulas
RangeFormula
C2:C3C2=WORKDAY.INTL(B2,1,"0111111")
D2:D3D2=WORKDAY.INTL(EDATE(B2,12),1,"1111110")
E2:E5E2=(D2-C2+1)/7


or
T202211a.xlsm
ABC
1
21-Apr-2252
31-Apr-2353
4
9b
Cell Formulas
RangeFormula
C2:C3C2=(WORKDAY.INTL(EDATE(B2-1,12),1,"1111110")-WORKDAY.INTL(B2-1,1,"0111111")+1)/7
 
Last edited:
Upvote 0
Solution
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! 👏🙏😊
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top