IF AND dates calculation

yorkam

New Member
Joined
Jun 12, 2014
Messages
12
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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:

Excel Formula:
=AND(EOMONTH(D$1,0)>=$B2,EOMONTH(D$1,-1)+1<=$C2)+0
 
Upvote 0
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

1728472191479.png
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,580
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top