BiggusDoggus
Board Regular
- Joined
- Jul 7, 2014
- Messages
- 91
- Office Version
- 365
- Platform
- Windows
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?
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?