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

[BTyner]

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!

 

[Sancdar]

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!

 

[BTyner]

That's exactly what I needed, thanks!