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: 3

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())
 

kemalwhitham97

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
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")
 

kemalwhitham97

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,425
Messages
5,642,042
Members
417,251
Latest member
Dordrecht

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
Top