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.

 

[Plasmech]

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!

@Eric W

This worked beautifully! Thank you so much.

I think I understand how it works now, too. A bit tricky, but I don't see an easier way.

Thanks again for putting forth all this effort.

Much appreciated!