# in year contained in Year Range

#### RayBan

##### New Member
I thought coding this would be dead easy, but can't get it right!

There's a range of years, say 1950 to 1970

Then another range 1945 to 1955.

I want to know if any year in the second range, exists in the first range. Have tried all sorts of < and > and juggling the
four year values but nothing works out correctly. Maybe there's a cleverer approach?

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### T. Valko

##### Well-known Member
Not real sure what you're looking for.

This formula will count how many years are within both time periods.

Data Range
 A​ B​ C​ D​ E​ 1​ Period 1​ 1950​ 1970​ ------​ In Both​ 2​ Period 2​ 1945​ 1955​ 6​

=MAX(0,MIN(C1,C2)-MAX(B1,B2)+1)

Last edited:

##### MrExcel MVP
 Row\Col A​ B​ C​ D​ E​ F​ G​ 1​ 2​ 1950​ 1970​ 1945​ 1955​ TRUE​ TRUE​ 3​

=AND(E2>=A2,D2<=B2)

Using brute force:

=ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(ROW(INDIRECT(D2&":"&E2)),ROW(INDIRECT(A2&":"&B2)),0)))

Replies
2
Views
231
Replies
6
Views
966
Replies
0
Views
303
Replies
9
Views
265
Replies
5
Views
485

1,191,718
Messages
5,988,267
Members
440,146
Latest member
rgomes8

### 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.

### Which adblocker are you using?

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

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