"Flag" the months between two dates

BiggusDoggus

Board Regular
Joined
Jul 7, 2014
Messages
88
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?
 

Some videos you may like

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
Joined
Jul 7, 2014
Messages
88
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,788
Messages
5,574,287
Members
412,584
Latest member
Leow
Top