# "Flag" the months between two dates

#### BiggusDoggus

##### Board Regular
Hi

I have a very large number of events, by row, which have a start date, and an end date.

And I have a separate table with months as the headers (Jan-18, Feb-18, etc, through to Oct-20)

What I'm wanting Excel to return is Y or N under each month, for each of the events, if they were "active" for even part of any of those months.

Example:

Start Date 12/4/20, End Date 31/5/20.

So, I want it to return Y for the months of Apr-20 & May-20 .

The formula I have been using is (example)

=IF(AND(C1>\$A2,C1<\$B2),"Y", FALSE)

C1 = Month, \$A2 = Start Date, \$B2 = End Date.

What it is returning is Y for May-20, but not for Apr-20.

I'm fairly certain the reason is because the months are actually 1/4/2020 & 1/5/2020, so it's saying since C1 (1/4/2020) is not greater than \$A2 (the Start Date, 12/4/20), then it's automatically False.

In other situations I could extract the month and year from the Start and End Dates, but that would result in a Text format, which doesn't work when using "Less than", "Greater than" etc.

Any ideas on how I can resolve this please?

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### BiggusDoggus

##### Board Regular
Never mind - sorted.

G2=\$A2-DAY(\$A2)+1 to convert Start Date (and same for End Date, H2) to 1st day of the month, then =IF(AND(C1>=\$G2,C1<=\$H2),"Y", FALSE)

Replies
1
Views
104
Replies
1
Views
260
Replies
8
Views
224
Replies
4
Views
124
Replies
8
Views
486