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

 

[Eurekaonide]

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

sorry my error, yes it should be a 1 in Oct

 

[Eurekaonide]

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

Yes apologies it should be a 1 in oct for the second row

 

[Fluff]

In that case the formula I supplied in post#2 does exactly what you asked for.

Fluff.xlsm
	A	B	C	D	E
1			31/10/2022	30/11/2022	31/12/2022
2	Start	End	01/10/2022	01/11/2022	01/12/2022
3	01/09/2022	01/11/2022	1	1
4	25/10/2022	31/10/2022	1
5	01/11/2022	01/12/2022		1	1
6	01/09/2022	28/09/2022
Main

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

 

[Eurekaonide]

In that case the formula I supplied in post#2 does exactly what you asked for.

Fluff.xlsm
	A	B	C	D	E
1			31/10/2022	30/11/2022	31/12/2022
2	Start	End	01/10/2022	01/11/2022	01/12/2022
3	01/09/2022	01/11/2022	1	1
4	25/10/2022	31/10/2022	1
5	01/11/2022	01/12/2022		1	1
6	01/09/2022	28/09/2022
Main

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

Thanks I dont know what im doing wrong then. It is still placing a 1 for a start date in September but Im looking in the column for October.

 

[Fluff]

Does the mini-sheet in post#13 show the correct answers?

 

[Eurekaonide]

Does the mini-sheet in post#13 show the correct answers?

Yes it does. Im really confused as to why it doesnt work. Ive checked the formats of the cells and everything it doesnt make sense.

 

[Fluff]

In that case check that your dates are real dates. If you change the format of all the date cells to general do you still see dates?

 

[Eurekaonide]

I think I may have solved it, would appreciate your feedback and thoughts.

=IF(OR(C$2>=$A3,C$1<=$A3,C$2>=$B3),0,1)

 

[Eurekaonide]

In that case check that your dates are real dates. If you change the format of all the date cells to general do you still see dates?

yes i tried that and they showed as the number formats. Ive just posted a formula I think is working, what do you think?

 

[Fluff]

That formula does not give the same results as post#13 which you said was correct.