Count of a persons name on a 30 day rolling period?

[kemalwhitham97]

Hello,

I am trying to develop and attendance sheet for my company which has a 3 call-offs/early leave policy, that is evaluated on a 30 day rolling period. I am trying to develop a formula to tell me if a write-up is needed, based on if the person has left early/come in late/called off 3 times within 30 days. I am currently using the formula

=COUNTIFS(B5:B999,">="&TODAY()-30,B5:B999,"<="&TODAY())

to evaluate the rolling period but am struggling nest it with the count function based on peoples names. Any help would be much appreciated.

Name	Date	Given By	Comments	Write-Up Needed?		Name1		Manager 1
Name1	1/1/21	Manager 1		=COUNTIFS(B5:B999,">="&TODAY()-30,B5:B999,"<="&TODAY())		Name2		Manager 2
Name1	1/2/21	Manager 2		29		Name3		Manager 3
Name4	1/3/21	Manager 3		28		Name4
Name6	1/4/21	Manager 3		27		Name5
Name4	1/5/21			26		Name6
Name9	1/6/21	Manager 2		25		Name7
Name5	1/7/21			24		Name8
Name5	1/8/21			23		Name9
Name6	1/9/21			22		Name10
Name7	1/10/21			21		Name11
Name2	1/11/21			20		Name12
Name17	1/12/21			19		Name13
Name18	1/13/21			18		Name14
Name1	1/14/21			17		Name15
	1/15/21			16		Name16
	1/16/21			15		Name17
	1/17/21			14		Name18
	1/18/21			13		Name19

Thanks,

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())

 

[kemalwhitham97]

Hi & welcome to MrExcel.
How about

=COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())

I appreciate the reply, That formula seems to just add a 1 or a 0 into the cell if the person was out 30 days prior.

Name	Date	Given By	Comments	Write-Up Needed?		Name1		Manager 1
Name1	1/1/21	Manager 1		1		Name2		Manager 2
Name1	1/2/21	Manager 2		1		Name3		Manager 3
Name1	1/3/21	Manager 3		1		Name4
Name1	2/4/21	Manager 3		0		Name5
Name1	2/5/21			0		Name6
Name1	2/6/21	Manager 2		0		Name7
Name5	2/7/21			0		Name8
Name5	2/8/21			0		Name9
Name6	2/9/21			0		Name10
Name7	2/10/21			0		Name11
Name2	2/11/21			1		Name12
Name17	2/12/21			0		Name13
Name18	2/13/21			0		Name14
Name1	2/14/21			0		Name15
	2/15/21			0		Name16
	2/16/21			0		Name17
	2/17/21			0		Name18
	2/18/21			0		Name19
	2/19/21			0
	2/20/21			0
	2/21/21			0

Ideally I am looking for the formula to look at the past 30 days (rolling from today) and count the number of times the name occurs. If that name occurs 3 or more times it will output "Yes" into the "Write-Up Needed?" cell and if not then it will output "No"

Hopefully this was helpful in clarifying.

Thanks

 

[Fluff]

With the data you have posted nobody appeared more than once in the last 30 days.

+Fluff 1.xlsm
	A	B	C	D	E
1
2
3
4	Name	Date	Given By	Comments	Write-Up Needed?
5	Name1	01/01/2021	Manager 1		Yes
6	Name1	02/01/2021	Manager 2		Yes
7	Name1	03/01/2021	Manager 3		Yes
8	Name1	04/01/2021	Manager 3		Yes
9	Name1	05/01/2021			Yes
10	Name1	06/02/2021	Manager 2		Yes
11	Name5	07/02/2021			No
12	Name5	08/02/2021			No
13	Name6	09/02/2021			No
14	Name7	10/02/2021			No
15	Name2	11/02/2021			No
16	Name17	12/02/2021			No
17	Name18	13/02/2021			No
18	Name1	14/02/2021			Yes
19		15/02/2021			No
20		16/02/2021			No
21		17/02/2021			No
22		18/02/2021			No
23		19/02/2021			No
24		20/02/2021			No
25		21/02/2021			No
Data

Cell Formulas
Range	Formula
E5:E25	E5	=IF(COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())>2,"Yes","No")

 

[kemalwhitham97]

With the data you have posted nobody appeared more than once in the last 30 days.

+Fluff 1.xlsm
	A	B	C	D	E
1
2
3
4	Name	Date	Given By	Comments	Write-Up Needed?
5	Name1	01/01/2021	Manager 1		Yes
6	Name1	02/01/2021	Manager 2		Yes
7	Name1	03/01/2021	Manager 3		Yes
8	Name1	04/01/2021	Manager 3		Yes
9	Name1	05/01/2021			Yes
10	Name1	06/02/2021	Manager 2		Yes
11	Name5	07/02/2021			No
12	Name5	08/02/2021			No
13	Name6	09/02/2021			No
14	Name7	10/02/2021			No
15	Name2	11/02/2021			No
16	Name17	12/02/2021			No
17	Name18	13/02/2021			No
18	Name1	14/02/2021			Yes
19		15/02/2021			No
20		16/02/2021			No
21		17/02/2021			No
22		18/02/2021			No
23		19/02/2021			No
24		20/02/2021			No
25		21/02/2021			No
Data

Cell Formulas
Range	Formula
E5:E25	E5	=IF(COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())>2,"Yes","No")

Works great! Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.