Formula to look between 2 dates

[Eurekaonide]

Hi
Wonder if you can help please.
I have a table with the months running along the top one month to each column (it defaults to the 1st of each month) so I have also added in above it the last day of each month.
so in column c row 1 it would say 31/10/2022 and in column c row 2 it would say 01/10/2022
Now in Row 3 column A is my start date and column B is an end date

what I want is for a formula to place a 1 in the C column (onwards) if the start date is between 1st-31st October and to place a 1 if the end date is between these dates.
this is so that each month it will show either a blank or a 1 depending on when something starts and finishes.

I had this but it doesnt seem to work

=IF(AND(A3<=C2,B3>=C1),1,"")

any help appreciated thanks

 

[Fluff]

How about

=IF(AND($A3<=C$1,$B3>=C$2),1,"")

 

[awoohaw]

Do you wnat either date to be in the month, or both dates to be in the month?
if either, then maybe this?

Mr Excel Questions.xlsx
	A	B	C	D
1			10/31/2022
2	Start	End	10/1/2022
3	9/5/2022	9/30/2022
4	10/5/2022	10/6/2022	1
5	10/25/2022	11/5/2022	1
6	11/5/2022	11/10/2022
7
Sheet7

Cell Formulas
Range	Formula
C3:C6	C3	=IF(OR( AND(A3>$C$2,A3<$C$1), AND(B3>$C$2,B3<$C$1) ),1,"")

 

[Eurekaonide]

Do you wnat either date to be in the month, or both dates to be in the month?
if either, then maybe this?

Mr Excel Questions.xlsx
	A	B	C	D
1			10/31/2022
2	Start	End	10/1/2022
3	9/5/2022	9/30/2022
4	10/5/2022	10/6/2022	1
5	10/25/2022	11/5/2022	1
6	11/5/2022	11/10/2022
7
Sheet7

Cell Formulas
Range	Formula
C3:C6	C3	=IF(OR( AND(A3>$C$2,A3<$C$1), AND(B3>$C$2,B3<$C$1) ),1,"")

Thanks I cant seem to get it to work.
what happens is
if the start date is before 01/10/2022 it puts it to blank which is correct but if the start date is 01/10/2022 if doesnt place a 1 the current end date is way out 31/12/2040 so I just want it to put a 1 in each column between dates I just cant seem to make it do it. I can if I just work on a start date but not when I want it to look at both start and end.

 

[Eurekaonide]

How about

=IF(AND($A3<=C$1,$B3>=C$2),1,"")

Thank you, its not working its still placing a 1 before the start date has started.

 

[Fluff]

In that case you need to do a better job of explaining what you want & at the same time post some sample data showing what you want.

 

[awoohaw]

If you want the First and Last Days of the month, then put "=" with the "<,>" signs.
=IF(OR( AND(A3>=$C$2,A3<=$C$1), AND(B3>=$C$2,B3<=$C$1) ),1,"")

To help us help you.. what do you want to see as the result in the possible scenarios that I have given with the solution I proposed?

		10/31/2022​
Start	End	10/1/2022​
05/09/2022​	30/09/2022​	????
15/09/2022​	25/10/2022​	????
01/10/2022​	20/10/2022​	????
05/10/2022​	06/10/2022​	????
25/10/2022​	31/10/2022​	????
28/10/2022​	25/11/2022​	????

 

[Eurekaonide]

If you want the First and Last Days of the month, then put "=" with the "<,>" signs.
=IF(OR( AND(A3>=$C$2,A3<=$C$1), AND(B3>=$C$2,B3<=$C$1) ),1,"")

To help us help you.. what do you want to see as the result in the possible scenarios that I have given with the solution I proposed?

		10/31/2022​
Start	End	10/1/2022​
05/09/2022​	30/09/2022​	"Blank" as not yet started
15/09/2022​	25/10/2022​	"Blank" as not yet started
01/10/2022​	20/10/2022​	1 as started within the month of October (would be a blank in the following month in Nov as it finished in Oct)
05/10/2022​	06/10/2022​	1 as started within the month of October (would be a blank in the following month in Nov as it finished in Oct)
25/10/2022​	31/10/2022​	1
28/10/2022​	25/11/2022​	1 as started in Oct and would also be a 1 in the next column for Nov but Dec would be blank

Thanks - hopefully the above helps.

 

[Fluff]

I don't understand why the 2nd row would be blank for Oct, but the last row would be 1 for Nov.

 

[awoohaw]

I don't understand why the 2nd row would be blank for Oct, but the last row would be 1 for Nov.

I agree, row 2 should be 1. It starts in Sept, Ends in Oct.