Count the number of days since employee last day of recorded absence/tardy

[nor1325]

I am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Date:[/TD]
[TD]1/1[/TD]
[TD]1/2[/TD]
[TD]1/3[/TD]
[TD]1/4[/TD]
[TD]1/5[/TD]
[TD]1/6[/TD]
[TD]1/7[/TD]
[TD]1/8[/TD]
[TD]1/9[/TD]
[TD]1/10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Captain America[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Incredible Hulk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spiderman[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Iron Man[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.

If anyone could give me a formula to use that would be great. Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!

Thank you!!

 

[Domenic]

Assuming that Sheet1!A1:L5 contains the data, first enter your names in Sheet2...

Sheet2!A2: Captain America

Sheet2!A3: Incredible Hulk

...and so on. Then enter the following formula in Sheet2!B2, and copy down:

=LOOKUP(2,1/ISNUMBER(MATCH(INDEX(Sheet1!$C$2:$L$5,MATCH(A2,Sheet1!$A$2:$A$5,0),0),{"A","T"},0)),Sheet1!$C$1:$L$1)

Adjust the ranges, accordingly. Actually, since you want to count the number of days since an employee's last absence or tardy, you probably want the following instead...

=TODAY()-LOOKUP(2,1/ISNUMBER(MATCH(INDEX(Sheet1!$C$2:$L$5,MATCH(A2,Sheet1!$A$2:$A$5,0),0),{"A","T"},0)),Sheet1!$C$1:$L$1)

 

[JoeMo]

Adjust the ranges to cover all data YTD.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Employee Name	Date:	1-Jan	2-Jan	3-Jan	4-Jan	5-Jan	6-Jan	7-Jan	8-Jan	9-Jan	10-Jan		Days since last A/T
2	Captain America			A					T					214
3	Incredible Hulk				T									218
4	Spiderman										A			212
5	Iron Man					A								217
6	Never Absent Guy													No A/T
Sheet5