# Does a specific date fall within a date range in a table of multiple date ranges?

#### BTyner

##### New Member
I have a table listing multiple date ranges with two columns: "Start" and "End". These date ranges never overlap, except maybe the end date of one record with the start date of the next record.

I need to transform these data into a table listing every day of the year in one column, and whether or not a given date falls within one of the ranges listed in the other table.

Original table:

Start End
1-2 1-4
1-7 1-8

New table:

Date Within Range?
1-1 No
1-2 Yes
1-3 Yes
1-4 Yes
1-5 No
1-6 No
1-7 Yes
1-8 Yes
1-9 No

etc.

I'm sure there are plenty of simple solutions to retrieve the "Within Range" yes/no column, but I'm at a loss.

I'm in a long-distance relationship, and my girlfriend is a data analyst. I'm trying to graph the days we spend together, because she'll get a kick out of it. Any advice would be much appreciated!

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I would do this with the COUNTIFS function, which lets you check a criteria against a range of cells.

Assuming your start dates are in A2 through A99 and end dates in B2 through B99, and your list of dates to check is column C with the "Within Range?" in column D:
<bdo dir="ltr">Put =IF(COUNTIFS(A\$2:A\$99, "<="&C2, B\$2:B\$99, ">="&C2)>0,"YES","NO")</bdo> in D2 and copy down.

This checks to see if your start date in any of the ranges is less than or equal to the date you're checking while the corresponding end date is greater than or equal to the date you're checking.
Hope this works for you!

That's exactly what I needed, thanks!

Replies
3
Views
144
Replies
3
Views
375
Replies
8
Views
1K
Replies
2
Views
242
Replies
3
Views
267

1,219,570
Messages
6,149,046
Members
450,853
Latest member
xtiinctt

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