calculating attendance percentage based on the number of days in a week worked

[Plasmech]

Say I have a spreadsheet with a column for the date and a column for the number of hours worked. The spreadsheet will be limited to one calendar year.

I want to add an "attendance" column. To achieve perfect (100% attendance), one must have hours (any nonzero number of hours) logged for Tuesday, Wednesday, and Thursday of each week.

example:

DATE HOURS ATT (%)
02/02/24 3 100
02/03/24 3 100
02/04/24 3 100
02/09/24 2 100
02/10/24 0 80
02/11/24 3 83.3

What would the best way to "code" this be?

Thanks.

 

[Eric W]

Assuming the dates are in order:

Book1
	A	B	C
1	Date	Hours	Att (%)
2	1/2/2024	3	100.00%
3	1/3/2024	2	100.00%
4	1/4/2024	3	100.00%
5	1/9/2024	2	100.00%
6	1/10/2024	0	80.00%
7	1/11/2024	3	83.33%
8	1/12/2024	1	83.33%
9	1/13/2024	1	83.33%
10	1/14/2024	1	83.33%
11	1/15/2024	1	83.33%
12	1/16/2024	0	71.43%
13	1/17/2024	1	75.00%
14	1/18/2024	1	77.78%
15	1/19/2024	1	77.78%
16	1/20/2024	1	77.78%
Sheet4

Cell Formulas
Range	Formula
C2:C16	C2	=ROWS(FILTER(A$2:A2,(WEEKDAY(A$2:A2,12)<4)*(B$2:B2>0)))/((WEEKNUM(A2,12)-2)*3+MIN(WEEKDAY(A2,12),3))

Named Ranges
Name	Refers To	Cells
RangeOne	=Sheet4!$B$3:$E$6	C3:C16

I also assumed your example was in January, since February 2,3,4 are not Tuesday through Thursday.

 

[felixstraube]

How about this?:

Book1
	A	B	C	D
1	Date	Hours
2	1/1/2024 Mon	3		72.6% (114 of 157)
3	1/2/2024 Tue	0
4	1/3/2024 Wed	3
5	1/4/2024 Thu	0
6	1/5/2024 Fri	3
7	1/6/2024 Sat	3
8	1/7/2024 Sun	3
9	1/8/2024 Mon	2
10	1/9/2024 Tue	1
11	1/10/2024 Wed	2
12	1/11/2024 Thu	2
13	1/12/2024 Fri	1
14	1/13/2024 Sat	2
15	1/14/2024 Sun	0
Sheet1

Cell Formulas
Range	Formula
A2:A367	A2	=SEQUENCE(366,,DATE(2024,1,1))
B2:B367	B2	=RANDARRAY(366,,0,3,1)
D2	D2	=LET( rd,A2:A3670, rh,B2:B3670, d, FILTER(rd,rd<>""), h, FILTER(rh,rh<>""), res,FILTER(h,d=WORKDAY.INTL(d-1,1,"1000111")), totalNumberOfDays, COUNT(res), workedDays, COUNT(FILTER(res,res<>0)), TEXT(workedDays/totalNumberOfDays,"0.0%") &" ("& workedDays &" of "&totalNumberOfDays & ")" )
Dynamic array formulas.

 

[Plasmech]

Assuming the dates are in order:

Book1
	A	B	C
1	Date	Hours	Att (%)
2	1/2/2024	3	100.00%
3	1/3/2024	2	100.00%
4	1/4/2024	3	100.00%
5	1/9/2024	2	100.00%
6	1/10/2024	0	80.00%
7	1/11/2024	3	83.33%
8	1/12/2024	1	83.33%
9	1/13/2024	1	83.33%
10	1/14/2024	1	83.33%
11	1/15/2024	1	83.33%
12	1/16/2024	0	71.43%
13	1/17/2024	1	75.00%
14	1/18/2024	1	77.78%
15	1/19/2024	1	77.78%
16	1/20/2024	1	77.78%
Sheet4

Cell Formulas
Range	Formula
C2:C16	C2	=ROWS(FILTER(A$2:A2,(WEEKDAY(A$2:A2,12)<4)*(B$2:B2>0)))/((WEEKNUM(A2,12)-2)*3+MIN(WEEKDAY(A2,12),3))

Named Ranges
Name	Refers To	Cells
RangeOne	=Sheet4!$B$3:$E$6	C3:C16

I also assumed your example was in January, since February 2,3,4 are not Tuesday through Thursday.

Hi Eric,

Thanks! This worked perfectly.

Yes, I meant to type January (01) dates. I tried to edit my post, but apparently, that function does not exist on this forum.

I am not familiar with "Names Ranges", nor did I need it/them to get your logic to work. Would you care to explain?

Thanks again.

 

[Plasmech]

How about this?:

Book1
	A	B	C	D
1	Date	Hours
2	1/1/2024 Mon	3		72.6% (114 of 157)
3	1/2/2024 Tue	0
4	1/3/2024 Wed	3
5	1/4/2024 Thu	0
6	1/5/2024 Fri	3
7	1/6/2024 Sat	3
8	1/7/2024 Sun	3
9	1/8/2024 Mon	2
10	1/9/2024 Tue	1
11	1/10/2024 Wed	2
12	1/11/2024 Thu	2
13	1/12/2024 Fri	1
14	1/13/2024 Sat	2
15	1/14/2024 Sun	0
Sheet1

Cell Formulas
Range	Formula
A2:A367	A2	=SEQUENCE(366,,DATE(2024,1,1))
B2:B367	B2	=RANDARRAY(366,,0,3,1)
D2	D2	=LET( rd,A2:A3670, rh,B2:B3670, d, FILTER(rd,rd<>""), h, FILTER(rh,rh<>""), res,FILTER(h,d=WORKDAY.INTL(d-1,1,"1000111")), totalNumberOfDays, COUNT(res), workedDays, COUNT(FILTER(res,res<>0)), TEXT(workedDays/totalNumberOfDays,"0.0%") &" ("& workedDays &" of "&totalNumberOfDays & ")" )
Dynamic array formulas.

Thank you, Felix. Very powerful. Thank you for putting the effort into this.

I need a running attendance, not a single, current value.

 

[felixstraube]

Oh, my mistake. Sorry.
Just curious. What are those percentages used for? Or how are they used later on?

 

[Eric W]

Hi Eric,

Thanks! This worked perfectly.

Yes, I meant to type January (01) dates. I tried to edit my post, but apparently, that function does not exist on this forum.

I am not familiar with "Names Ranges", nor did I need it/them to get your logic to work. Would you care to explain?

Thanks again.

Sorry about the named range! That was leftover from another problem I was working on, on the same sheet. It's not visible on the sheet, so I didn't notice it until after I posted. Just ignore it, it has nothing to do with your answer.

Anyway, glad we could help!

 

[Plasmech]

Sorry about the named range! That was leftover from another problem I was working on, on the same sheet. It's not visible on the sheet, so I didn't notice it until after I posted. Just ignore it, it has nothing to do with your answer.

Anyway, glad we could help!

Hi Eric,

Sorry to bother you again. How would I changed the days from Tue, Wed, Thurs to Tue, Wed, Sat?

In addition to actually needing the days changed, this will help me understand how the logic works.

Thanks.

 

[Plasmech]

Oh, my mistake. Sorry.
Just curious. What are those percentages used for? Or how are they used later on?

Hey Felix,

They are used in a personal spreadsheet. I use it to keep track of a side project.

 

[Eric W]

Hi Eric,

Sorry to bother you again. How would I changed the days from Tue, Wed, Thurs to Tue, Wed, Sat?

In addition to actually needing the days changed, this will help me understand how the logic works.

Thanks.

Tues, Wed, Sat makes it trickier! But it's not too big of a change, and maybe it makes it a bit easier to understand. Try:

Book1
	A	B	C	D
1	Date	Hours	Att (%)
2	1/2/2024	3	100.00%	Tuesday
3	1/3/2024	2	100.00%	Wednesday
4	1/4/2024	3	100.00%	Thursday
5	1/9/2024	2	75.00%	Tuesday
6	1/10/2024	0	60.00%	Wednesday
7	1/11/2024	3	60.00%	Thursday
8	1/12/2024	1	60.00%	Friday
9	1/13/2024	1	66.67%	Saturday
10	1/14/2024	1	66.67%	Sunday
11	1/15/2024	1	66.67%	Monday
12	1/16/2024	0	57.14%	Tuesday
13	1/17/2024	1	62.50%	Wednesday
14	1/18/2024	1	62.50%	Thursday
15	1/19/2024	1	62.50%	Friday
16	1/20/2024	1	66.67%	Saturday
17			#DIV/0!
Sheet4

Cell Formulas
Range	Formula
D2:D16	D2	=TEXT(A2,"dddd")
C2:C17	C2	=SUM(ISNUMBER(MATCH(WEEKDAY(A$2:A2),{3,4,7},0))*(B$2:B2>0))/((WEEKNUM(A2)-1)*3+VLOOKUP(WEEKDAY(A2),{1,0;3,1;4,2;7,3},2))

A percentage is basically a ratio of valid days divided by total number of possible days. The numerator part of the formula is:

SUM(ISNUMBER(MATCH(WEEKDAY(A$2:A2),{3,4,7},0))*(B$2:B2>0))

The A$2:A2 construct is basically an expanding range as you drag the formula down the column, so it includes the current row, and all the rows above it. The MATCH looks at the WEEKDAY of each date in column A, and if it is 3, 4, or 7 it returns a number, otherwise an error. The ISNUMBER turns that into TRUE or FALSE. Then the B$2:B2>0 part looks at the hours to make sure they're >0 and returns TRUE or FALSE. When you multiply those together, you get 1 or 0, and the SUM adds up the 1s. These are the valid days. (In the original formula I used FILTER, which still works, but this is a tad shorter.)

To get the denominator,

((WEEKNUM(A2)-1)*3+VLOOKUP(WEEKDAY(A2),{1,0;3,1;4,2;7,3},2))

The WEEKNUM gives you the week of the year so far. Subtract 1, and that's the number of previous weeks, multiply by 3 (for Tue,Wed,Sat) to get the number of possible days before this week. The VLOOKUP tells us how many possible days are in the current week. The embedded table basically says that Sun-Mon, we add 0. Tue, we add 1. Wed-Fri we add 2, Sat we add 3. I don't especially like the embedded table, but it's better than a complicate IF structure.

So that's the gist of it! Hope it helps!