IF AND MONTH

[excelquestioner123]

I am trying to make a formula based on if someone replied in the month. So if the name is listed anywhere on the sheet but the date in the same row in December for example. I have tried everything from the Month function to greater than less than function.
=IF(AND('DATA INPUT'!$A:$A='Observers'!$A2,MONTH('DATA INPUT'!$B:$B=MONTH(12))),"Yes","No")
=IF(AND('DATA INPUT'!$A:$A='Observers'!$A24,('DATA INPUT'!B:B>"11/31/2020"),('DATA INPUT'!B:B<"1/1/2021")),"Yes","No")

 

[gigidica]

Can you supply an example?

 

[JamesCanale]

Maybe:

MrExcelPlayground.xlsm
	A	B	C	D	E	F
1	Name	Date		Name	Month	Did person have record in month?
2	Jamie	11/2/2020		Fred	Jan-21	yes
3	Jamie	11/22/2020		Jamie	Jan-21	no
4	Jamie	11/24/2020		Jamie	Nov-20	yes
5	Jamie	12/19/2020		Goose	Feb-21	yes
6	Fred	12/28/2020		Goose	Jan-21	no
7	Fred	1/6/2021		Maverick	Jan-21	no
8	Fred	1/23/2021		Maverick	Mar-21	yes
9	Fred	2/1/2021
10	Harry	2/7/2021
11	Goose	2/16/2021
12	Goose	3/9/2021
13	Maverick	3/21/2021
14	Maverick	3/26/2021
15	Maverick	4/20/2021
16	Maverick	4/24/2021
Sheet29

Cell Formulas
Range	Formula
F2:F8	F2	=IF(SUMPRODUCT(--($A$2:$A$16=D2),--(DATE(YEAR($B$2:$B$16),MONTH($B$2:$B$16),1)=E2))>0,"yes","no")

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(COUNTIFS('DATA INPUT'!$A:$A,A2,'DATA INPUT'!B:B,">"&DATE(2020,31,11),'DATA INPUT'!B:B,"<"&DATE(2021,1,1)),"yes","No")

 

[excelquestioner123]

 

[excelquestioner123]

Hi & welcome to MrExcel.
How about

=IF(COUNTIFS('DATA INPUT'!$A:$A,A2,'DATA INPUT'!B:B,">"&DATE(2020,31,11),'DATA INPUT'!B:B,"<"&DATE(2021,1,1)),"yes","No")

This one didn't work for me. Could it be because I am pulling from a different sheet and using the entire column?

 

[excelquestioner123]

Maybe:

MrExcelPlayground.xlsm
	A	B	C	D	E	F
1	Name	Date		Name	Month	Did person have record in month?
2	Jamie	11/2/2020		Fred	Jan-21	yes
3	Jamie	11/22/2020		Jamie	Jan-21	no
4	Jamie	11/24/2020		Jamie	Nov-20	yes
5	Jamie	12/19/2020		Goose	Feb-21	yes
6	Fred	12/28/2020		Goose	Jan-21	no
7	Fred	1/6/2021		Maverick	Jan-21	no
8	Fred	1/23/2021		Maverick	Mar-21	yes
9	Fred	2/1/2021
10	Harry	2/7/2021
11	Goose	2/16/2021
12	Goose	3/9/2021
13	Maverick	3/21/2021
14	Maverick	3/26/2021
15	Maverick	4/20/2021
16	Maverick	4/24/2021
Sheet29

Cell Formulas
Range	Formula
F2:F8	F2	=IF(SUMPRODUCT(--($A$2:$A$16=D2),--(DATE(YEAR($B$2:$B$16),MONTH($B$2:$B$16),1)=E2))>0,"yes","no")

This one didn't work for me!

 

[Fluff]

This one didn't work for me.

In what way?

 

[gigidica]

I don't know if you have the possibility to create this alternative, but what I did, I added 2 columns to the right of the date column (one for month - =MONTH(B2) and one for year - =YEAR(B2)) and then I used a combo of if and countifs:
Cell I2 =IF(COUNTIFS($A$2:$A$16,E2,$C$2:$C$16,MONTH(F2),$D$2:$D$16,YEAR(F2))>0,"YES","NO")

 

[Alex Blakenburg]

This one didn't work for me. Could it be because I am pulling from a different sheet and using the entire column?

1) Try the below modified version of Fluff's formula
Modification was change DATE(2020,31,11) to DATE(2020,11,31)
format is Date(Year,Month,Day)

See here:-

Excel Formula:
=IF(COUNTIFS('DATA INPUT'!$A:$A,A2,'DATA INPUT'!B:B,">"&DATE(2020,11,31),'DATA INPUT'!B:B,"<"&DATE(2021,1,1)),"yes","No")

2) Assuming you are putting the formula in the sheet Inpatient Observers, its not failing because its on a different sheet,