Help with Attendance Tracker - Percentage of Dates

[SGMiller]

I'm trying to create a workbook that tracks attendance. The main goal is to see how often a person has attended over a period of time based on the current date. I have come up blank on how to accomplish this. Could anyone provide any insight? The data would be in one worksheet as shown below.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[/TR]
[TR]
[TD]Joe Black[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Stacy Smith[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

Ideal goal:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Last week[/TD]
[TD]Last 2 weeks[/TD]
[TD]Last 3 weeks[/TD]
[TD]Overall[/TD]
[/TR]
[TR]
[TD]Joe Black[/TD]
[TD]100%[/TD]
[TD]50%[/TD]
[TD]66%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]Stacy Smith[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance, Scott

 

[jorismoerings]

Hi and welcome to Mrexcel,

It's bit vague but take a look at this and see if this is something you can use:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F
1	Name	7-1-2017	23-1-2017	30-1-2017
2	Joe Black	Yes	No	Yes
3	Stacy Smith	Yes	Yes	Yes
4
5
6
7	Name	This Week	Last week	Last 2 weeks	Last 3 weeks	Overall
8	Joe Black	14%	0%	0%	0%	6%
9	Stacy Smith	14%	14%	0%	0%	10%
Sheet1

Cell Formulas
Range	Formula
E8	=SUMPRODUCT(--(WEEKNUM($B$1:$D$1+0)=WEEKNUM(NOW())-COLUMN()+2)*($A$2:$A$3=$A8)*($B$2:$D$3="Yes"))/7
F8	=SUMPRODUCT(($A$2:$A$3=$A8)*($B$2:$D$3="Yes"))/DAY(EOMONTH($B$1,0))