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!!!

 

[6StringJazzer]

This won't be hard but the solution will be 100% dependent on what data you have and how it's organized. You need to show what your Excel file looks like. How are you indicating that an employee work on a Friday?

The strategy is going to be to use a SUMIF formula for any given date to sum the points that occur during the one year prior to that date. But there is no way to give you a formula without seeing your sheet.

 

[yankat22]

This won't be hard but the solution will be 100% dependent on what data you have and how it's organized. You need to show what your Excel file looks like. How are you indicating that an employee work on a Friday?

The strategy is going to be to use a SUMIF formula for any given date to sum the points that occur during the one year prior to that date. But there is no way to give you a formula without seeing your sheet.

Okay, I've attached a portion of the excel spreadsheet. let me know if this is sufficient. Thank you so much for your help!

practicing formulas.xlsx
	VL	VM	VN	VO	VP	VQ	VR	VS	VT																																												XL	XM	XN	XO	XP	XQ
1		Sat	Sun	Mon	Tue	Wed	Thr	Fri	Sat																																												Current Schedule
2		11/12/22	11/13/22	11/14/22	11/15/22	11/16/22	11/17/22	11/18/22	11/19/22																																												Mondays	Fridays	Total	RTOs	Lates	Shifts
3	smith		13	13	13																																																3	3	6	0	2	18
4	miller				13	13		13																																													4	3	7	0	2	18
5	jones						13	13																																													2	2	4	0	0	8
6	johnson	13			13	13			13																																												2	2	4	0	2	18
7	garcia			13		13	13																																														3	3	6	0	2	18
Schedule

Cell Formulas
Range	Formula
XL3:XL7	XL3	=COUNTIF(UD3,">0")+COUNTIF(UL3,">0")+COUNTIF(US3,">0")+COUNTIF(UZ3,">0")+COUNTIF(VG3,">0")+COUNTIF(VO3,">0")+COUNTIF(UD3,"2100A")+COUNTIF(UL3,"2100A")+COUNTIF(US3,"2100A")+COUNTIF(UZ3,"2100A")+COUNTIF(VG3,"2100A")+COUNTIF(VO3,"2100A")+COUNTIF(UD3,"2100B")+COUNTIF(UL3,"2100B")+COUNTIF(US3,"2100B")+COUNTIF(UZ3,"2100B")+COUNTIF(VG3,"2100B")+COUNTIF(VO3,"2100B")
XM3:XM7	XM3	=COUNTIF(UI3,">0")+COUNTIF(UP3,">0")+COUNTIF(UW3,">0")+COUNTIF(VD3,">0")+COUNTIF(VK3,">0")+COUNTIF(VS3,">0")+COUNTIF(UI3,"2100A")+COUNTIF(UP3,"2100A")+COUNTIF(UW3,"2100A")+COUNTIF(VD3,"2100A")+COUNTIF(VK3,"2100A")+COUNTIF(VS3,"2100A")+COUNTIF(UI3,"2100B")+COUNTIF(UP3,"2100B")+COUNTIF(UW3,"2100B")+COUNTIF(VD3,"2100B")+COUNTIF(VK3,"2100B")+COUNTIF(VS3,"2100B")
XN3:XN7	XN3	=XL3+XM3
XO3:XO7	XO3	=COUNTIF(UD3:VT3,"RTO*")
XP3:XP7	XP3	=COUNTIF(UD3:VT3,"2100A")+COUNTIF(UD3:VT3,"2100B")+COUNTIF(UD3:VT3,2130)
XQ3:XQ7	XQ3	=COUNTIF(UD3:VT3,"2100A")+COUNTIF(UD3:VT3,"2100B")+COUNTIF(UD3:VT3,"2130")+COUNTIF(UD3:VT3,13)+COUNTIF(UD3:VT3,"24")+COUNTIF(UD3:VT3,"12p")+COUNTIF(UD3:VT3,"2130*")+COUNTIF(UD3:VT3,10)+COUNTIF(UD3:VT3,11)

 

[6StringJazzer]

This is progress. However, this only shows one week. Your formula to count Fridays seem to start in column UI and checks 6 weeks. The ">0" part makes sense but what do 2100B and 2100A mean? What column has the earliest data, and what is your earliest date?

Where do you want to show these points that are accumulated? Do you want to have one cell that has the current points, or a cell for each week that shows points for the year up to that week?

 

[yankat22]

This is progress. However, this only shows one week. Your formula to count Fridays seem to start in column UI and checks 6 weeks. The ">0" part makes sense but what do 2100B and 2100A mean? What column has the earliest data, and what is your earliest date?

Where do you want to show these points that are accumulated? Do you want to have one cell that has the current points, or a cell for each week that shows points for the year up to that week?

The entire year would be "too many cells" to paste but I was going to create a column by the Friday column to keep a total of the points for each individual. Depending on the formula, I was going to just calculate the total for the entire row for each person. 2100A and 2100b is a designation we use for our late shifts that are totalled for the day.

 

[yankat22]

This is progress. However, this only shows one week. Your formula to count Fridays seem to start in column UI and checks 6 weeks. The ">0" part makes sense but what do 2100B and 2100A mean? What column has the earliest data, and what is your earliest date?

Where do you want to show these points that are accumulated? Do you want to have one cell that has the current points, or a cell for each week that shows points for the year up to that week?

Earliest date would be their start date which varies by individual.

 

[yankat22]

This is progress. However, this only shows one week. Your formula to count Fridays seem to start in column UI and checks 6 weeks. The ">0" part makes sense but what do 2100B and 2100A mean? What column has the earliest data, and what is your earliest date?

Where do you want to show these points that are accumulated? Do you want to have one cell that has the current points, or a cell for each week that shows points for the year up to that week?

Hi there, any progress or insight on how to accomplish this?

 

[6StringJazzer]

I was going to create a column by the Friday column to keep a total of the points for each individual.

Not sure what this means.

I am expecting to see a single cell for each person that shows the "points" for the most recent rolling year. Is that what XM is supposed to be? Seems unlikely since your numbers are so low. To write a formula for that cell I would need to know how the entire year is laid out. I don't need to see the entire sheet but I at least need a description of it. Your example shows that there is data in at least 590+ columns and I have no idea what is in columns A:VK.

I stepped back because I was asking pretty specific questions but not getting responses that are concrete in terms of your sheet. I need to know what data is in what cells.

 

[yankat22]

Not sure what this means.

I am expecting to see a single cell for each person that shows the "points" for the most recent rolling year. Is that what XM is supposed to be? Seems unlikely since your numbers are so low. To write a formula for that cell I would need to know how the entire year is laid out. I don't need to see the entire sheet but I at least need a description of it. Your example shows that there is data in at least 590+ columns and I have no idea what is in columns A:VK.

I stepped back because I was asking pretty specific questions but not getting responses that are concrete in terms of your sheet. I need to know what data is in what cells.

I'm so sorry, I didn't realize I wasn't answering your question. I really appreciate your help. XM is the amount of Fridays for that specific 6 week block. The excel sheet is a continuous calendar starting at 01/01/22 through 12/31/22 but I only make 6 weeks visible at a time (for sign ups). The totals at the end of those 6 weeks are the totals for the specified days for those 6 weeks (3 Mondays, 2 Fridays, 1 late shift for those 6 weeks). Again, I really appreciate your help. The part that most confuses me is how to automate the drop of at the anniversary date (the person's hire date every year). Thank you again.

 

[6StringJazzer]

automate the drop of at the anniversary date (the person's hire date every year)

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?