Data validation with 2 number ranges

[shirleyxls]

I am working on a project tracking spreadsheet and developing a summary sheet where the user can select a project and report on the income, spend, etc data which is stored in tables on various other sheets. The projects run for several weeks, some of which span two years. I want the user to be able to enter the week number for which they want to see the details, but also need to validate that they have chosen a week covered by the project.

If the project is all in the same year, I can use Whole Number validation and just check the entry is between the start and end week. But if the project spans two years the validation needs to check 2 number ranges. For example, if the project starts in week 25, 2016 and ends in week 18, 2017 the validation needs to permit numbers 25 to 52 or 1 to 18.

Note that a project can never span more than 52 weeks.

Any ideas on if this is possible, or will I just need to include the year as part of the validation criteria?

I've attached a small sample workbook.

[Phuocam]

Try this ...

=OR(AND(C10>=C5,C10<=52),AND(C10>0,C10<=C6))

Or try this ...

=OR((C10>=C5)*(C10<=52),(C10>0)*(C10<=C6))

[shirleyxls]

Perfect! Obvious when I think about it

Many thanks for your help

[Pete_UK]

Maybe you should treat them as a continuous range, i.e. from week 25 (in 2016) to week 70 (also counted as if from 2016), then you can use MOD(..., 52) to get the year offset.

Hope this helps.

Pete

[samba_ravi]

=IF(D5=D6,AND(C10>=C5,C10<=C6),IF(D6-D5=1,OR(C10>=C5,C10<=C6),AND(C10>0,C10<53)))

Try this formula in data validation