Calculate points and have them drop off after one year

[yankat22]

I'm not sure if this is possible on excel but if I wanted to count how many Fridays an employee worked to assign points and then have them automatically drop off after the anniversary date of that point, what would that formula look like?

Example:
Sarah worked 2 Fridays in October 2022, 1 Friday in November, and so on for a full year. I want to total all of those Fridays and then when it's been a year since October 2022, I want those October dates to start dropping off and her points to go down accordingly.

Let me know if that's possible or if I need to elaborate further.

Thank you in advance!!!

 

[yankat22]

The plot thickens. In your first post I did not know what you meant by "anniversary date," because you title says something different. Your title makes it sound like you want any point to drop off one year after it happened. Now it sounds like you want all points to drop to zero on each anniversary-of-hire date.

So what cell contains the person's anniversary date? It seems like what you mean when you say "automatically drop off" is to count the Fridays since the most recent anniversary date.

Also it sounds like you are showing me the Friday count in column XM for 6 weeks. Where do you want the cumulative count for all points accrued since the hire anniversary date?

Yes! Exactly what I would like, I'm sorry if I didn't explain it adequately. I would like them to drop off at the anniversary-of-hire date. Well, I don't have any cells that contain that info (anniversary date), would I need one? Right now the cells before the start date are blank for each individual that started during this year (nov 2021 - nov 2022). I guess I could create more columns to the side of the existing totaling columns (monday/friday/late shifts etc columns) that can have the year to date information in them, one for the late shifts total for the last year, and one for the weekend shifts for the year.
Would I need to create a column that has everyone's anniversary date?

 

[6StringJazzer]

You either need a column with anniversary date, or give specific details on how to determine what it is from existing data.

the cells before the start date are blank for each individual that started during this year

What about employees who started before this year? How do you know their anniversary dates?

 

[yankat22]

Okay, thank you, I apologize for my absence due to the holidays. I hope you had a good Thanksgiving as well . How about this for the formatting?

draft.xlsx
	TV	TW	TX	TY	TZ	UA	UB	UC																																																																																						XK	XL	XM	XN	XO	XP	XQ	XR	XS
1		Sun	Mon	Tue	Wed	Thr	Fri	Sat																																																																																						Current Schedule
2		10/2/22	10/3/22	10/4/22	10/5/22	10/6/22	10/7/22	10/8/22																																																																																						Mondays	Fridays	Total	RTOs	Lates	Wknd Shift	Night Shift	Anniversary Date	Shifts
3	Apple		2100a	13	13																																																																																									3	3	6	0	4	1	0	9/12/21	18
4	Bannana	PTO1	PTO1	PTO1	PTO1	PTO1	PTO1	PTO1																																																																																						3	3	6	0	2	1	0	10/18/20	15
5	Candy		2100b																																																																																											1	1	2	0	2	0	0	12/24/21	7
6	Devin		12p		13	2100B		13																																																																																						2	3	5	4	4	2	1	10/10/19	19
7	Elephant		13	13			13																																																																																							3	4	7	1	2	0	0	5/14/15	15
8	Ferris				2100B		13																																																																																							2	3	5	1	4	1	0	10/12/22	14
Schedule

Cell Formulas
Range	Formula
XK3:XK8	XK3	=COUNTIF(SN3,">0")+COUNTIF(SU3,">0")+COUNTIF(TB3,">0")+COUNTIF(TI3,">0")+COUNTIF(TP3,">0")+COUNTIF(TX3,">0")+COUNTIF(SN3,"2100A")+COUNTIF(SU3,"2100A")+COUNTIF(TB3,"2100A")+COUNTIF(TI3,"2100A")+COUNTIF(TP3,"2100A")+COUNTIF(TX3,"2100A")+COUNTIF(SN3,"2100B")+COUNTIF(SU3,"2100B")+COUNTIF(TB3,"2100B")+COUNTIF(TI3,"2100B")+COUNTIF(TP3,"2100B")+COUNTIF(TX3,"2100B")
XL3:XL8	XL3	=COUNTIF(SR3,">0")+COUNTIF(SY3,">0")+COUNTIF(TF3,">0")+COUNTIF(TM3,">0")+COUNTIF(TT3,">0")+COUNTIF(UB3,">0")+COUNTIF(SR3,"2100A")+COUNTIF(SY3,"2100A")+COUNTIF(TF3,"2100A")+COUNTIF(TM3,"2100A")+COUNTIF(TT3,"2100A")+COUNTIF(UB3,"2100A")+COUNTIF(SR3,"2100B")+COUNTIF(SY3,"2100B")+COUNTIF(TF3,"2100B")+COUNTIF(TM3,"2100B")+COUNTIF(TT3,"2100B")+COUNTIF(UB3,"2100B")
XM3:XM8	XM3	=XK3+XL3
XN3:XN8	XN3	=COUNTIF(SM3:UC3,"RTO*")
XO3:XO8	XO3	=COUNTIF(SM3:UC3,"2100A")+COUNTIF(SM3:UC3,"2100B")+COUNTIF(SM3:UC3,2130)
XP3:XP8	XP3	=COUNTIF(SM3,10)+COUNTIF(SS3:ST3,10)+COUNTIF(SZ3:TA3,10)+COUNTIF(TG3:TH3,10)+COUNTIF(TN3:TO3,10)+COUNTIF(TU3:TW3,10)+COUNTIF(UC3,10)+COUNTIF(SM3,13)+COUNTIF(SS3:ST3,13)+COUNTIF(SZ3:TA3,13)+COUNTIF(TG3:TH3,13)+COUNTIF(TN3:TO3,13)+COUNTIF(TU3:TW3,13)+COUNTIF(UC3,13)
XQ3:XQ8	XQ3	=COUNTIF(SM3:UC3,"12P")+COUNTIF(SM3:UC3,24)
XS3:XS8	XS3	=COUNTIF(SM3:UC3,"2100A")+COUNTIF(SM3:UC3,"2100B")+COUNTIF(SM3:UC3,"2130")+COUNTIF(SM3:UC3,13)+COUNTIF(SM3:UC3,"24")+COUNTIF(SM3:UC3,"12p")+COUNTIF(SM3:UC3,"2130*")+COUNTIF(SM3:UC3,10)+COUNTIF(SM3:UC3,11)