Excel Formula to calculate threshold

[gd6noob]

Hi everyone,

Just had a query with trying to calculate some formula.

As you can see in the table below, I want to calculate the % of the employee that will be away to the total of headcount of each manager. So in J2 for Alice on Mar 1, there is 1 person that will be absent for 2 days. So if Today is Mar 1, J2 would be 2% of the headcount. the Max threshold for any Manger should not exceed 10%(indicated in J1). If today is Mar 3, that person will be back and Alice would be at 0%.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Manager	Time Off	Date	# of Days			Manager	Total Headcount	10%
2	Alan	Alice	Vacation	15-Mar	10			Alice	50
3	Bob	Alice	Sick	01-Mar	4			Kate	40
4	charlie	Alice	Personal Day	10-Mar	2
5	Debbie	Kate	Vacation	15-Mar	5
6	Erin	Kate	Sick	01-Mar	1
7	Frank	Kate	Personal Day	10-Mar	1
Sheet1

I hope I've explained it clearly, if not, let me know.

 

[etaf]

I have changed some dates & Headcount - so it works with todays date - using today() , as all the dates where in future
I have also added a helper column "F" for the End data of the absence , using workday() - so only Mon-Fri - I'm sure we could use something without that helper - but it was quick
If its 7 days a week , then just add the number to the startdate

so to find out the number of people that are off as of this day
i used
=COUNTIFS($B$2:$B$7,H2,$D$2:$D$7,"<="&TODAY(),$F$2:$F$7,">="&TODAY())

then wrapped that with the headcount to get a %
=COUNTIFS($B$2:$B$7,H2,$D$2:$D$7,"<="&TODAY(),$F$2:$F$7,">="&TODAY())/I2

You could also add an IF
=IF((COUNTIFS($B$2:$B$7,H2,$D$2:$D$7,"<="&TODAY(),$F$2:$F$7,">="&TODAY())/I2)>J$1,"Exceeds Target",COUNTIFS($B$2:$B$7,H2,$D$2:$D$7,"<="&TODAY(),$F$2:$F$7,">="&TODAY())/I2)

Various.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Manager	Time Off	Date	# of Days	End		Manager	Total Headcount	0.1		Count
2	Alan	Alice	Vacation	2/16/22	10	3/2/22		Alice	50	4%		0
3	Bob	Alice	Sick	2/27/22	4	3/3/22		Kate	6	Exceeds Target		0
4	charlie	Alice	Personal Day	3/10/22	2	3/14/22
5	Debbie	Kate	Vacation	2/26/22	5	3/4/22				TRUE	3/2/22	2
6	Erin	Kate	Sick	2/27/22	1	2/28/22				TRUE		3
7	Frank	Kate	Personal Day	2/10/22	30	3/24/22				FALSE		0
8										TRUE		0
9										TRUE		0
10										TRUE		0
11										FALSE		0
Sheet17

Cell Formulas
Range	Formula
J2:J3	J2	=IF((COUNTIFS($B$2:$B$7,H2,$D$2:$D$7,"<="&TODAY(),$F$2:$F$7,">="&TODAY())/I2)>J$1,"Exceeds Target",COUNTIFS($B$2:$B$7,H2,$D$2:$D$7,"<="&TODAY(),$F$2:$F$7,">="&TODAY())/I2)
L2:L3	L2	=COUNTIFS($B$2:$B$7,H2,$D$2:$D$7,">="&TODAY(),$F$2:$F$7,"<="&TODAY())
K5	K5	=WORKDAY(D2,E2)
D6	D6	=TODAY()
F2:F7	F2	=WORKDAY(D2,E2)
J5:J11	J5	=AND(TODAY()>=D2,TODAY()<=WORKDAY(D2,E2))
L5:L11	L5	=COUNTIFS($B$2:$B$7,H2,$D$2:$D$7,"<="&TODAY(),$F$2:$F$7,">="&TODAY())

 

[gd6noob]

Thank you @etaf this will really help me.

 

[etaf]

you are welcome

 

[gd6noob]

Hi @etaf, just a follow up question,

Using the table you provided, I would like to calculate the threshold for future state as well. e.g. the threshold on a weekly basis.
Im unable to come up with the formula myself, would you beable to provide some assistance?

Time Off Tracker.xlsb
	N	O	P
14	Week Number	Total HC	0.1
15	1	75
16	2	75
17	3	75
18	4	75
19	5	75
20	6	75
21	7	75
22	8	75
23	9	75
24	10	75
25	11	75
26	12	75
27	13	75
28	14	75
29	15	75
30	16	75
31	17	75
32	18	75
33	19	75
34	20	75
35	21	75
36	22	75
37	23	75
38	24	75
39	25	75
40	26	75
41	27	75
42	28	75
43	29	75
44	30	75
45	31	75
46	32	75
47	33	75
48	34	75
49	35	75
50	36	75
51	37	75
52	38	75
53	39	75
54	40	75
55	41	75
56	42	75
57	43	75
58	44	75
59	45	75
60	46	75
61	47	75
62	48	75
63	49	75
64	50	75
65	51	75
66	52	75
67	53	75
2022

 

[etaf]

it maybe worth starting a new question , so other members can also help, as the data will need to be broken down into weeknumbers
for instance the 10working days will cross a number of weeknumbers

i'm not sure the best way to break that info down
for example
15th march for 10days - is over 3 weeknumbers
4 day - week12
5 days - week13
1 days - week14

and so crosses 3 of you weeks

 

[gd6noob]

Thanks, will start a new topic. Thank you.