Using "IF" with "AND" and "OR"

[jpemberton]

I must have something wrong with my formula. I'm trying to find out if a donor is eligible to make a donation each month. To be eligible they have to be approved and not retired. So to be eligible to make a deposit in January, they have to be approved on or before January 1 and not retired before January 31. For Column E (G or I), if C is less than or equal to the first of that month and B is less than or equal to the last of the month or blank, return Yes. It's not working for rows 13-45. Any idea what I've got wrong? Any help is appreciated. There are actually 1500+ rows in my data and I really don't want to have to do this manually each month.

donor-01.12.22.csv
	A	B	C	D	E	F	G	H	I
1				45	45	33	45	12	45
2	Donor ID	Retired Date	Second Approval Date	Should Return	1-Jan	Should Return	1-Feb	Should Return	Mar-20
3	9985	3/2/2020	6/21/2019	Yes	Yes	Yes	Yes	Yes	Yes
4	10012	3/2/2020	6/21/2019	Yes	Yes	Yes	Yes	Yes	Yes
5	10037	3/2/2020	6/28/2019	Yes	Yes	Yes	Yes	Yes	Yes
6	10042	3/2/2020	7/8/2019	Yes	Yes	Yes	Yes	Yes	Yes
7	10055	3/2/2020	7/17/2019	Yes	Yes	Yes	Yes	Yes	Yes
8	10206	3/2/2020	9/25/2019	Yes	Yes	Yes	Yes	Yes	Yes
9	10342	3/2/2020	11/21/2019	Yes	Yes	Yes	Yes	Yes	Yes
10	10363	3/2/2020	11/25/2019	Yes	Yes	Yes	Yes	Yes	Yes
11	10353	3/2/2020	12/12/2019	Yes	Yes	Yes	Yes	Yes	Yes
12	10378	3/2/2020	12/12/2019	Yes	Yes	Yes	Yes	Yes	Yes
13	10379	3/2/2020	12/24/2019	Yes	Yes	Yes	Yes	Yes	Yes
14	10322	2/28/2020	11/15/2019	Yes	Yes	Yes	Yes	No	Yes
15	9863	2/27/2020	4/25/2019	Yes	Yes	Yes	Yes	No	Yes
16	9890	2/25/2020	5/20/2019	Yes	Yes	Yes	Yes	No	Yes
17	9904	2/25/2020	5/22/2019	Yes	Yes	Yes	Yes	No	Yes
18	9957	2/14/2020	6/6/2019	Yes	Yes	Yes	Yes	No	Yes
19	10089	2/14/2020	8/21/2019	Yes	Yes	Yes	Yes	No	Yes
20	10118	2/14/2020	8/21/2019	Yes	Yes	Yes	Yes	No	Yes
21	10188	2/14/2020	10/18/2019	Yes	Yes	Yes	Yes	No	Yes
22	10196	2/14/2020	10/18/2019	Yes	Yes	Yes	Yes	No	Yes
23	10248	2/14/2020	10/18/2019	Yes	Yes	Yes	Yes	No	Yes
24	10007	2/14/2020	10/25/2019	Yes	Yes	Yes	Yes	No	Yes
25	10239	2/14/2020	10/25/2019	Yes	Yes	Yes	Yes	No	Yes
26	10250	2/14/2020	10/30/2019	Yes	Yes	Yes	Yes	No	Yes
27	10303	2/14/2020	10/31/2019	Yes	Yes	Yes	Yes	No	Yes
28	10278	2/14/2020	12/12/2019	Yes	Yes	Yes	Yes	No	Yes
29	9915	2/12/2020	6/21/2019	Yes	Yes	Yes	Yes	No	Yes
30	10096	2/12/2020	8/9/2019	Yes	Yes	Yes	Yes	No	Yes
31	10284	2/12/2020	10/25/2019	Yes	Yes	Yes	Yes	No	Yes
32	10446	2/12/2020	12/30/2019	Yes	Yes	Yes	Yes	No	Yes
33	9969	2/11/2020	6/6/2019	Yes	Yes	Yes	Yes	No	Yes
34	10161	2/11/2020	8/27/2019	Yes	Yes	Yes	Yes	No	Yes
35	9921	1/28/2020	7/17/2019	Yes	Yes	No	Yes	No	Yes
36	10050	1/28/2020	7/17/2019	Yes	Yes	No	Yes	No	Yes
37	10044	1/28/2020	8/19/2019	Yes	Yes	No	Yes	No	Yes
38	10135	1/28/2020	8/19/2019	Yes	Yes	No	Yes	No	Yes
39	10139	1/28/2020	8/21/2019	Yes	Yes	No	Yes	No	Yes
40	10141	1/28/2020	8/28/2019	Yes	Yes	No	Yes	No	Yes
41	10229	1/28/2020	9/30/2019	Yes	Yes	No	Yes	No	Yes
42	10285	1/28/2020	10/30/2019	Yes	Yes	No	Yes	No	Yes
43	10260	1/8/2020	10/18/2019	Yes	Yes	No	Yes	No	Yes
44	10238	1/8/2020	10/30/2019	Yes	Yes	No	Yes	No	Yes
45	10187	1/6/2020	10/18/2019	Yes	Yes	No	Yes	No	Yes
46	10264	1/6/2020	10/18/2019	Yes	Yes	No	Yes	No	Yes
47	10289		10/25/2019	Yes	Yes	Yes	Yes	Yes	Yes
48	10637	6/24/2020	4/6/2020	No	No	No	No	No	No
49	10842	7/1/2020	5/30/2020	No	No	No	No	No	No
50	10694	7/1/2020	4/10/2020	No	No	No	No	No	No
51	10782	6/29/2020	5/15/2020	No	No	No	No	No	No
donor-01.12.22 (2)

Cell Formulas
Range	Formula
D1:I1	D1	=COUNTIF(D2:D1283,"Yes")
E3:E51	E3	=IF(AND(C3>=DATEVALUE("1/31/2020"),OR(B3>=DATEVALUE("1/01/2020"),B3="")),"No","Yes")
G3:G51	G3	=IF(AND(C3>=DATEVALUE("2/29/2020"),OR(B3>=DATEVALUE("2/01/2020"),B3="")),"No","Yes")
I3:I51	I3	=IF(AND(C3>=DATEVALUE("3/31/2020"),OR(B3>=DATEVALUE("3/01/2020"),B3="")),"No","Yes")

 

[etaf]

=AND($C3<=E$2,OR($B3>EOMONTH(E$2,0),$B3=""))

AND(C3>=DATEVALUE("1/31/2020"),OR(B3>=DATEVALUE("1/01/2020"),B3="")

I may have miss-read, as i have used B where you use C
and C where you used B

they have to be approved on or before January 1
Approved before C has to be earlier then E2
$C3<=E$2

and not retired before January 31.
Retired is B so > eomonth or blank
OR ( B3>EOMONTH(E$2,0),B3="")

so i have
=AND($C3<=E$2,OR($B3>EOMONTH(E$2,0),$B3=""))

BUT all the dates need changing as they come over as text on my system and I can change without retyping them all

 

[jtakw]

Hi,

Fixed your formulas for D, E,F Columns.
I suggest replacing your current formulas with my suggested formula in H3, it's simpler, easier to maintain, and you just drag it across your sheet for future months:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1				45	33	12
2	Donor ID	Retired Date	Second Approval Date	1/1/2020	2/1/2020	3/1/2020
3	9985	3/2/2020	6/21/2019	Yes	Yes	Yes		Yes	Yes	Yes
4	10012	3/2/2020	6/21/2019	Yes	Yes	Yes		Yes	Yes	Yes
5	10037	3/2/2020	6/28/2019	Yes	Yes	Yes		Yes	Yes	Yes
6	10042	3/2/2020	7/8/2019	Yes	Yes	Yes		Yes	Yes	Yes
7	10055	3/2/2020	7/17/2019	Yes	Yes	Yes		Yes	Yes	Yes
8	10206	3/2/2020	9/25/2019	Yes	Yes	Yes		Yes	Yes	Yes
9	10342	3/2/2020	11/21/2019	Yes	Yes	Yes		Yes	Yes	Yes
10	10363	3/2/2020	11/25/2019	Yes	Yes	Yes		Yes	Yes	Yes
11	10353	3/2/2020	12/12/2019	Yes	Yes	Yes		Yes	Yes	Yes
12	10378	3/2/2020	12/12/2019	Yes	Yes	Yes		Yes	Yes	Yes
13	10379	3/2/2020	12/24/2019	Yes	Yes	Yes		Yes	Yes	Yes
14	10322	2/28/2020	11/15/2019	Yes	Yes	No		Yes	Yes	No
15	9863	2/27/2020	4/25/2019	Yes	Yes	No		Yes	Yes	No
16	9890	2/25/2020	5/20/2019	Yes	Yes	No		Yes	Yes	No
17	9904	2/25/2020	5/22/2019	Yes	Yes	No		Yes	Yes	No
18	9957	2/14/2020	6/6/2019	Yes	Yes	No		Yes	Yes	No
19	10089	2/14/2020	8/21/2019	Yes	Yes	No		Yes	Yes	No
20	10118	2/14/2020	8/21/2019	Yes	Yes	No		Yes	Yes	No
21	10188	2/14/2020	10/18/2019	Yes	Yes	No		Yes	Yes	No
22	10196	2/14/2020	10/18/2019	Yes	Yes	No		Yes	Yes	No
23	10248	2/14/2020	10/18/2019	Yes	Yes	No		Yes	Yes	No
24	10007	2/14/2020	10/25/2019	Yes	Yes	No		Yes	Yes	No
25	10239	2/14/2020	10/25/2019	Yes	Yes	No		Yes	Yes	No
26	10250	2/14/2020	10/30/2019	Yes	Yes	No		Yes	Yes	No
27	10303	2/14/2020	10/31/2019	Yes	Yes	No		Yes	Yes	No
28	10278	2/14/2020	12/12/2019	Yes	Yes	No		Yes	Yes	No
29	9915	2/12/2020	6/21/2019	Yes	Yes	No		Yes	Yes	No
30	10096	2/12/2020	8/9/2019	Yes	Yes	No		Yes	Yes	No
31	10284	2/12/2020	10/25/2019	Yes	Yes	No		Yes	Yes	No
32	10446	2/12/2020	12/30/2019	Yes	Yes	No		Yes	Yes	No
33	9969	2/11/2020	6/6/2019	Yes	Yes	No		Yes	Yes	No
34	10161	2/11/2020	8/27/2019	Yes	Yes	No		Yes	Yes	No
35	9921	1/28/2020	7/17/2019	Yes	No	No		Yes	No	No
36	10050	1/28/2020	7/17/2019	Yes	No	No		Yes	No	No
37	10044	1/28/2020	8/19/2019	Yes	No	No		Yes	No	No
38	10135	1/28/2020	8/19/2019	Yes	No	No		Yes	No	No
39	10139	1/28/2020	8/21/2019	Yes	No	No		Yes	No	No
40	10141	1/28/2020	8/28/2019	Yes	No	No		Yes	No	No
41	10229	1/28/2020	9/30/2019	Yes	No	No		Yes	No	No
42	10285	1/28/2020	10/30/2019	Yes	No	No		Yes	No	No
43	10260	1/8/2020	10/18/2019	Yes	No	No		Yes	No	No
44	10238	1/8/2020	10/30/2019	Yes	No	No		Yes	No	No
45	10187	1/6/2020	10/18/2019	Yes	No	No		Yes	No	No
46	10264	1/6/2020	10/18/2019	Yes	No	No		Yes	No	No
47	10289		10/25/2019	Yes	Yes	Yes		Yes	Yes	Yes
48	10637	6/24/2020	4/6/2020	No	No	No		No	No	No
49	10842	7/1/2020	5/30/2020	No	No	No		No	No	No
50	10694	7/1/2020	4/10/2020	No	No	No		No	No	No
51	10782	6/29/2020	5/15/2020	No	No	No		No	No	No
Sheet958

Cell Formulas
Range	Formula
D1:F1	D1	=COUNTIF(D2:D1283,"Yes")
D3:D51	D3	=IF(AND(C3<=DATEVALUE("1/31/2020"),OR(B3>=DATEVALUE("1/01/2020"),B3="")),"Yes","No")
E3:E51	E3	=IF(AND(C3<=DATEVALUE("2/29/2020"),OR(B3>=DATEVALUE("2/01/2020"),B3="")),"Yes","No")
F3:F51	F3	=IF(AND(C3<=DATEVALUE("3/31/2020"),OR(B3>=DATEVALUE("3/01/2020"),B3="")),"Yes","No")
H3:J51	H3	=IF(AND($C3<=EOMONTH(D$2,0),OR($B3>=D$2,$B3="")),"Yes","No")

 

[jpemberton]

Thank you for the help, guys!!

 

[jtakw]

In re-reading your Written description, please use this updated formula in D3 copied down and across:

Book3.xlsx
	A	B	C	D	E	F
1				45	33	12
2	Donor ID	Retired Date	Second Approval Date	1/1/2020	2/1/2020	3/1/2020
3	9985	3/2/2020	6/21/2019	Yes	Yes	Yes
4	10012	3/2/2020	6/21/2019	Yes	Yes	Yes
5	10037	3/2/2020	6/28/2019	Yes	Yes	Yes
6	10042	3/2/2020	7/8/2019	Yes	Yes	Yes
7	10055	3/2/2020	7/17/2019	Yes	Yes	Yes
8	10206	3/2/2020	9/25/2019	Yes	Yes	Yes
9	10342	3/2/2020	11/21/2019	Yes	Yes	Yes
10	10363	3/2/2020	11/25/2019	Yes	Yes	Yes
11	10353	3/2/2020	12/12/2019	Yes	Yes	Yes
12	10378	3/2/2020	12/12/2019	Yes	Yes	Yes
13	10379	3/2/2020	12/24/2019	Yes	Yes	Yes
14	10322	2/28/2020	11/15/2019	Yes	Yes	No
15	9863	2/27/2020	4/25/2019	Yes	Yes	No
16	9890	2/25/2020	5/20/2019	Yes	Yes	No
17	9904	2/25/2020	5/22/2019	Yes	Yes	No
18	9957	2/14/2020	6/6/2019	Yes	Yes	No
19	10089	2/14/2020	8/21/2019	Yes	Yes	No
20	10118	2/14/2020	8/21/2019	Yes	Yes	No
21	10188	2/14/2020	10/18/2019	Yes	Yes	No
22	10196	2/14/2020	10/18/2019	Yes	Yes	No
23	10248	2/14/2020	10/18/2019	Yes	Yes	No
24	10007	2/14/2020	10/25/2019	Yes	Yes	No
25	10239	2/14/2020	10/25/2019	Yes	Yes	No
26	10250	2/14/2020	10/30/2019	Yes	Yes	No
27	10303	2/14/2020	10/31/2019	Yes	Yes	No
28	10278	2/14/2020	12/12/2019	Yes	Yes	No
29	9915	2/12/2020	6/21/2019	Yes	Yes	No
30	10096	2/12/2020	8/9/2019	Yes	Yes	No
31	10284	2/12/2020	10/25/2019	Yes	Yes	No
32	10446	2/12/2020	12/30/2019	Yes	Yes	No
33	9969	2/11/2020	6/6/2019	Yes	Yes	No
34	10161	2/11/2020	8/27/2019	Yes	Yes	No
35	9921	1/28/2020	7/17/2019	Yes	No	No
36	10050	1/28/2020	7/17/2019	Yes	No	No
37	10044	1/28/2020	8/19/2019	Yes	No	No
38	10135	1/28/2020	8/19/2019	Yes	No	No
39	10139	1/28/2020	8/21/2019	Yes	No	No
40	10141	1/28/2020	8/28/2019	Yes	No	No
41	10229	1/28/2020	9/30/2019	Yes	No	No
42	10285	1/28/2020	10/30/2019	Yes	No	No
43	10260	1/8/2020	10/18/2019	Yes	No	No
44	10238	1/8/2020	10/30/2019	Yes	No	No
45	10187	1/6/2020	10/18/2019	Yes	No	No
46	10264	1/6/2020	10/18/2019	Yes	No	No
47	10289		10/25/2019	Yes	Yes	Yes
48	10637	6/24/2020	4/6/2020	No	No	No
49	10842	7/1/2020	5/30/2020	No	No	No
50	10694	7/1/2020	4/10/2020	No	No	No
51	10782	6/29/2020	5/15/2020	No	No	No
Sheet958

Cell Formulas
Range	Formula
D1:F1	D1	=COUNTIF(D2:D1283,"Yes")
D3:F51	D3	=IF(AND($C3<=D$2,OR($B3>=D$2,$B3="")),"Yes","No")