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

kemalwhitham97

New Member
Joined
Feb 2, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
Excel 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.

NameDateGiven ByCommentsWrite-Up Needed?Name1Manager 1
Name11/1/21Manager 1=COUNTIFS(B5:B999,">="&TODAY()-30,B5:B999,"<="&TODAY())Name2Manager 2
Name11/2/21Manager 229Name3Manager 3
Name41/3/21Manager 328Name4
Name61/4/21Manager 327Name5
Name41/5/2126Name6
Name91/6/21Manager 225Name7
Name51/7/2124Name8
Name51/8/2123Name9
Name61/9/2122Name10
Name71/10/2121Name11
Name21/11/2120Name12
Name171/12/2119Name13
Name181/13/2118Name14
Name11/14/2117Name15
1/15/2116Name16
1/16/2115Name17
1/17/2114Name18
1/18/2113Name19

Thanks,
 

Attachments

  • Attendance sheet.PNG
    Attendance sheet.PNG
    31.9 KB · Views: 22

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi & welcome to MrExcel.
How about
Excel Formula:
=COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())
 
Upvote 0
Hi & welcome to MrExcel.
How about
Excel Formula:
=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.

NameDateGiven ByCommentsWrite-Up Needed?Name1Manager 1
Name11/1/21Manager 11Name2Manager 2
Name11/2/21Manager 21Name3Manager 3
Name11/3/21Manager 31Name4
Name12/4/21Manager 30Name5
Name12/5/210Name6
Name12/6/21Manager 20Name7
Name52/7/210Name8
Name52/8/210Name9
Name62/9/210Name10
Name72/10/210Name11
Name22/11/211Name12
Name172/12/210Name13
Name182/13/210Name14
Name12/14/210Name15
2/15/210Name16
2/16/210Name17
2/17/210Name18
2/18/210Name19
2/19/210
2/20/210
2/21/210

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
 
Upvote 0
With the data you have posted nobody appeared more than once in the last 30 days.
+Fluff 1.xlsm
ABCDE
1
2
3
4NameDateGiven ByCommentsWrite-Up Needed?
5Name101/01/2021Manager 1Yes
6Name102/01/2021Manager 2Yes
7Name103/01/2021Manager 3Yes
8Name104/01/2021Manager 3Yes
9Name105/01/2021Yes
10Name106/02/2021Manager 2Yes
11Name507/02/2021No
12Name508/02/2021No
13Name609/02/2021No
14Name710/02/2021No
15Name211/02/2021No
16Name1712/02/2021No
17Name1813/02/2021No
18Name114/02/2021Yes
1915/02/2021No
2016/02/2021No
2117/02/2021No
2218/02/2021No
2319/02/2021No
2420/02/2021No
2521/02/2021No
Data
Cell Formulas
RangeFormula
E5:E25E5=IF(COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())>2,"Yes","No")
 
Upvote 0
With the data you have posted nobody appeared more than once in the last 30 days.
+Fluff 1.xlsm
ABCDE
1
2
3
4NameDateGiven ByCommentsWrite-Up Needed?
5Name101/01/2021Manager 1Yes
6Name102/01/2021Manager 2Yes
7Name103/01/2021Manager 3Yes
8Name104/01/2021Manager 3Yes
9Name105/01/2021Yes
10Name106/02/2021Manager 2Yes
11Name507/02/2021No
12Name508/02/2021No
13Name609/02/2021No
14Name710/02/2021No
15Name211/02/2021No
16Name1712/02/2021No
17Name1813/02/2021No
18Name114/02/2021Yes
1915/02/2021No
2016/02/2021No
2117/02/2021No
2218/02/2021No
2319/02/2021No
2420/02/2021No
2521/02/2021No
Data
Cell Formulas
RangeFormula
E5:E25E5=IF(COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())>2,"Yes","No")
Works great! Thanks!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,335
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top