Calculate time

[lachone]

Hello.
I need to know the formula I need to calculate how many days it will take a student to get from the given absenteeism rate down to 10.

• School is 6.5 hours a day
• The numbers are in general format

I thought about putting some other formula (school day formula) that adjusts automatically (like the today formula), only counting Monday - Friday. I haven't been able to figure that out yet.

TODAY FORMULA HERE​	SCHOOL DAY FORMULA HERE ?​
STUDENT​	OCT​	DAYS​	NOV​	DAYS​
YOLANDA	20.88%		22.34%
JAMES	15.06%		12.44%
NICOLE	62.02%		11.06%
ANTHONY	35.73%		27.25%
PARIS	12.2%		25.10%
MICHAEL	11.6%		14.59%
NOA	15.21%		24.01%

 

[Rijnsent]

Hi there, can you elaborate a bit?
So if I look at YOLANDA, she has a 20.88% absentee rate in October and 22.34% in november. In October there were 31 days, but only 23 weekdays. So say you have 23 weekdays with 6.5 hours of lessons, that is 23*6.5=149.5 hours of lessons. With a 10% absentee rate, people can miss maximum of 14.95 hours. And looking at YOLANDA, she probably missed about 34.2 hours. So say you start calculating from October, you are probably looking for a formula where 34.2 equals 10% and than calculates till what date people have to have a 100% show rate to get the absent rate down to 10%?

Is that what do you want to calculate? The number of hours someone missed? Please give some practical examples and desired outcomes, that helps helping you to find the right formula.

 

[lachone]

Hi there, can you elaborate a bit?
So if I look at YOLANDA, she has a 20.88% absentee rate in October and 22.34% in november. In October there were 31 days, but only 23 weekdays. So say you have 23 weekdays with 6.5 hours of lessons, that is 23*6.5=149.5 hours of lessons. With a 10% absentee rate, people can miss maximum of 14.95 hours. And looking at YOLANDA, she probably missed about 34.2 hours. So say you start calculating from October, you are probably looking for a formula where 34.2 equals 10% and than calculates till what date people have to have a 100% show rate to get the absent rate down to 10%?

Is that what do you want to calculate? The number of hours someone missed? Please give some practical examples and desired outcomes, that helps helping you to find the right formula.

Hello!
I think you're correct. I want to provide the actual scenario, just to be sure I'm communicating correctly.
In order to get a job, the students' absenteeism rate has to be 10% or below. The most common question I get is "How many days will I have to go to school and class on time before I can get my work permit"? Since school is 6.5 hours a day, how many days will it take for the student to get to their 10%.

Hi there, can you elaborate a bit?
So if I look at YOLANDA, she has a 20.88% absentee rate in October and 22.34% in november. In October there were 31 days, but only 23 weekdays. So say you have 23 weekdays with 6.5 hours of lessons, that is 23*6.5=149.5 hours of lessons. With a 10% absentee rate, people can miss maximum of 14.95 hours. And looking at YOLANDA, she probably missed about 34.2 hours. So say you start calculating from October, you are probably looking for a formula where 34.2 equals 10% and than calculates till what date people have to have a 100% show rate to get the absent rate down to 10%?

Is that what do you want to calculate? The number of hours someone missed? Please give some practical examples and desired outcomes, that helps helping you to find the right formula.

 

[lachone]

I'm not understanding your response. What additional information can I give you in order to help me with the correct formula?

 

[Rijnsent]

Okay, this is probably a step-by-step calculation that you're looking for. I was asking for an example of what your desired outcome would be. I hope this is clear, if not, please do let me know.

temp_calc1.xlsx
	A	B	C	D	E	F	G	H	I	J
1		DAYS	HOURS
2		23	150					31-okt-24		holidays
3	STUDENT	1-okt-24	hours_missed	hours_ok	hours_to_10%	hours_to_go	days_to_go	date		31-12-2024
4	YOLANDA	20,88%	31,2	118	312	163	25	5-dec-24		1-1-2025
5	JAMES	15,06%	22,5	127	225	76	12	15-nov-24
6	NICOLE	62,02%	92,7	57	927	778	120	18-apr-25
7	ANTHONY	35,73%	53,4	96	534	385	59	24-jan-25
8	PARIS	12,20%	18,2	131	182	33	5	7-nov-24
9	MICHAEL	11,60%	17,3	132	173	24	4	5-nov-24
10	NOA	15,21%	22,7	127	227	78	12	15-nov-24
Blad1

Cell Formulas
Range	Formula
B2	B2	=NETWORKDAYS(B3,EOMONTH(B3,0),holidays)
C2	C2	=B2*6.5
H2	H2	=EOMONTH(B3,0)
C4	C4	=B4*$C$2
D4:D10	D4	=$C$2-C4
E4:E10	E4	=C4/0.1
F4:F10	F4	=E4-D4-C4
G4:G10	G4	=F4/6.5
H4:H10	H4	=WORKDAY($H$2,G4,holidays)
C5:C10	C5	=B5*$B$2*6.5

Named Ranges
Name	Refers To	Cells
holidays	=Blad1!$J$3:$J$4	B2, H4:H10

 

[lachone]

Hello again!
I am very confused and I feel bad because you're really trying to help. I thought we could go about this a different way. So, there are 80 days of school left (starting on February 03, 2025). With that information, are we able to simplify the formula? We have school 6.5 hours a day - 520 hours left in the year - How many days will it take to decrease current attendance rate down to 10% or below. I don't know if this helps or not but I do have a TODAY formula in cell A3.