Fomula of dinamic values inside an array

[drom]

Hi and thanks in advance!

If I wanna know the Week Number of a quarter, I can use, the formula:

The Dates are in Column D
=WEEKNUM(D3;1)-WEEKNUM((LOOKUP(D3;DATE(YEAR(D3);{2;5;8;11};1)));1)+1

But in my company the first quarter stars the 1st of november, the 2nd quarter the 1st of february, the 3rd the 1st of may and the 4th the 1st of august

so I have to modify the prior formula



I do not know how can I change the array created inside the lookup

LOOKUP(D3;DATE(YEAR(D3-1);11;1)&";"&DATE(YEAR(D3);2;1)&";"&DATE(YEAR(D3);5;1)&";"&DATE(YEAR(D3);8;1))

including { } does not work
to make it work

 

[Tetra201]

See if this works for you:

=ROUNDUP(D3/7;0)-ROUNDUP(EDATE(EOMONTH(D3;-1)+1;-MOD(MONTH(D3)+1;3))/7;0)+1