Calculating the week number when you have a fiscal year starting in May

[louise_hegedus]

Hi - I am looking for a simple formula to work out the week number for my dates in our fiscal year.
Column A is a range of different dates for the last 3 years
For example= A1 is 01/5/2016 I want it to return in cell B1 the week number 1

Any help would be great!

Thanks!!!

 

[JPARKHURST]

In the subject you state your fiscal year begins in May, but in the body you are asking for January to be week 1.

Does May matter, or are you simply looking for the week number?

=WeekNum(a1,1)

Where A1 is your date and 1 is the week beginning on Sunday.

 

[louise_hegedus]

Sorry I am in the UK so my date in A1 would be 05/01/2016 as week 1

 

[jtakw]

Hi,

Try this:

Book1
	A	B	C
1	5/1/2016		1
Sheet77

Cell Formulas
Range	Formula
C1	=WEEKNUM(A1)-WEEKNUM(EOMONTH(A1,-1)-6)

 

[Tetra201]

@ jtakw:

Your solution returns 1 for the first day of every month after May and negative numbers for January.

Here is my take on the fiscal year week number problem:

=ROUNDDOWN((A1+7-WORKDAY.INTL(DATE(YEAR(A1)-(MONTH(A1)<5),5,2),-1,"1111110"))/7,0)

 

[jtakw]

Thanks Tetra, for some reason, I was Only thinking May 1st at the time...

 

[louise_hegedus]

Thanks Tetra! This worked perfectly!

Just looking at the data again, I think instead of doing up to 52/53 weeks for the year I just need to show how many weeks for that quarter.
So for example:
Q1 = May - July (14 weeks based off the formula above)
Q2 = August - October (X weeks)
Q3 = November - Jan
Q4= Feb - April

Any help for this formula would be amazing! Thanks!