Trying to build an ARRAY formula with OR conditions that returns result based on a range of numbers

[nbalak]

So I've got this far:
{=IFERROR(INDEX($B$9:$B$496,SMALL(IF($C$9:$C$496>G$507, +IF($C$9:$C$496<g$508,row($b$9:$b$496)-row($b$9)+1)),rows($b$9:$b9))),"")}

We are dealing with a list of Standing Order payments from customers. Each has a different day of the month. I need a way of finding which customers fall into a relevant week.

If you imagine that column B holds names
Column holds C holds the day number

For each week of the year I have the lowest day number in G507 and the highest day number in G508.
So for example if G507 = 7 and G508 = 15 it will return a list of all clients whose standing order day falls within the days numbered 8 - 14.

The above works EXCEPT when it comes to cross overs. So where the week starts on day 30 and ends on day 6...

Any suggestions?</g$508,row($b$9:$b$496)-row($b$9)+1)),rows($b$9:$b9))),"")}

 

[mole999]

have you tried =Weeknum( date cell), that provides a consistency within the year. It will show up to 53 weeks at times but that is I believe ISO standard compliant

also if you repost your formula, you need to put spaces either side of < to allow it to display (the board thinks its an HTML instruction)

 

[nbalak]

no I hadn't thought of that...

How would I insert that into the array formula? Do I also need to change the way I record the day numbers in column C? Currently they are just the day number..

 

[nbalak]

Also it looks like I didnt post the whole formula on my last post...

=IFERROR(INDEX($B$9:$B$496,SMALL(IF($C$9:$C$496>G$507, +IF($C$9:$C$496 < G$508,ROW($B$9:$B$496)-ROW($B$9)+1)),ROWS($B$9:$B9))),"")