Need Help With Formula

[khedger]

I am trying to come up with a formula that will tell me when someone should be written up according to our policy. I have attached a copy of our policy and a copy of our excel spreadsheet that tracks tardies and unexcused absences. I'm struggling to find a formula that would fit. I'd like it to tell me when they have earned enough incidents within a certain time period and then show me the result, such as "1st Written Warning" or whatever the corresponding discipline is. Can anyone help??

 

[DRSteele]

Welcome to the forum.

If you would post some sample data together with expected result, we could conduct experiments. See my signature below to find out how.

 

[khedger]

So, in the example I posted, John has had a total of 10 incidents within the last 10 months, which means I should get a result of "Possible Termination". We have a rolling schedule so the Tardy from 2/6/16 & 4/20/16 should be dropped off because it has been more than 10 months. Does this make sense? I need to know when an employee hits a certain mark and what level that would put them at, but I need the old dates (dates past 10 months) to drop off. Not sure if this helps....

 

[63falcondude]

How's this?

Excel 2010
	A	B	C	D	E	F	G	H
1	# of Incidents	Time Frame (mo.)	Disciplinary Action		Employee Name	Start Date	Type of Leave	Incidents
2	2	2	Document Reminder		O'Donnell, John	2/16/2016		0.5
3	3	4	1st Written Warning		O'Donnell, John	4/20/2016		0.5
4	4	6	2nd Written Warning		O'Donnell, John	5/18/2016		1
5	5	8	Sent home without pay		O'Donnell, John	6/16/2016		1
6	6	10	Termination		O'Donnell, John	7/8/2016		0.5
7					O'Donnell, John	8/10/2016		1
8	Employee Name	Incidents in past 10 months	Action		O'Donnell, John	10/10/2016		1
9	O'Donnell, John	10	Termination		O'Donnell, John	11/14/2016		1
10					O'Donnell, John	11/29/2016		1
11					O'Donnell, John	12/1/2016		1
12					O'Donnell, John	1/10/2017		0.5
13					O'Donnell, John	2/27/2017		1
14					O'Donnell, John	3/6/2017		0.5
15					O'Donnell, John	3/15/2017		0.5
Sheet1

Cell Formulas
Range	Formula
B9	=SUMIF(F:F,">="&EDATE(TODAY(),-10),H:H)
C9	=LOOKUP(B9,B2:B6,C2:C6)