IF AND dates calculation

[yorkam]

Hi
I'm in need of some solutions.
I have a long list of activities in column A each one has a start and end date which are in columns B and C respectively.
Across the top row I have each month of the year January Feb etc.. in same date format.
I would like a formula to put a 1 in the cell where the activity starts and 0 if not.

I've tried IF(AND..... but works for first month but not afterwards..

So for example
Task 1 start date 13/3/24 end date 20/11/24
So I would expect
Jan 0 Feb 0 mar to November 1 and Dec 0

Any ideas solutions much appreciated.

L

 

[RoryA]

Assuming your data starts in row 2 with date headers starting in D1 and going across (and that those are real dates), try something like:

=AND(EOMONTH(D$1,0)>=$B2,EOMONTH(D$1,-1)+1<=$C2)+0

 

[Special-K99]

You're gonna need the year in that top row as well as the month.
What happens if an activity starts in Dec 2024 and goes over to Feb 2025.
You dont want 1s in Jan and Feb do you? Hence the requirement of a year as well.
Formula in D2 should be

=AND(D$1>=($B2-DAY($B2)+1),D$1<=EOMONTH($C2,0))*1

Row 1 beginning at D1 is Jan-23 Feb-23 Mar-23 etc (Note the dates will all be 1st of the month, e.g. 01/01/23 1/02/23 etc)
where A2 is activity desciption
B2 is start date
C2 is end date

 

[yorkam]

You're gonna need the year in that top row as well as the month.
What happens if an activity starts in Dec 2024 and goes over to Feb 2025.
You dont want 1s in Jan and Feb do you? Hence the requirement of a year as well.
Formula in D2 should be

=AND(D$1>=($B2-DAY($B2)+1),D$1<=EOMONTH($C2,0))*1

Row 1 beginning at D1 is Jan-23 Feb-23 Mar-23 etc (Note the dates will all be 1st of the month, e.g. 01/01/23 1/02/23 etc)
where A2 is activity desciption
B2 is start date
C2 is end date

View attachment 117879

 

[yorkam]

Both many thanks for the prompt reply. Problem solved have a good day