Counting number of days for each employee since last day of 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.

...................................1/1 ....1/2 ....1/3 ....1/4 ....1/5 ....1/6 ....1/7 ....1/8 ....1/9 ....1/10 ....1/11 ....1/12 ....1/13

Captain America ................................A ..........................T .....................................T
Incredible Hulk.......................................................................................... A
Spider Man ................................................................................................................................A
Iron Man ....................................................T

( I added dots because it wouldn't let me space them out?)


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!!

 

[PawelOrlinski]

Hi no1325, can you please attach a screenshot of your spreadsheet? From what you wrote I don't understand how it shoul look like.
Best

 

[PawelOrlinski]

Ok, I got it. I found a simple solution: make a table in which you will count how many days an employee was without infraction. Here is the screenshot (this link goes to my student website):
http://students.mimuw.edu.pl/~po281665/Excel/spreadsheet.JPG
Do you want your spreadsheet to work this way?

 

[markmzz]

Maybe this:

Formulas:

 A9-> =$A2

 B9-> =$N$1-MAX(MAX(INDEX(($B$2:$N$5<>"")*$B$1:$N$1,MATCH(A9,$A$2:$A$5,0))),$B$1-1)

Layout:
[TABLE="width: 540"]
<tbody>[TR]
[TD="class: xl67"][/TD]
[TD="class: xl67, width: 87, bgcolor: #D9D9D9"]A
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]B
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]C
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]D
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]E
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]F
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]G
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]H
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]I
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]J
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]K
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]L
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]M
[/TD]
[TD="class: xl67, width: 47, bgcolor: #D9D9D9"]N
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]1
[/TD]
[TD="class: xl66, bgcolor: transparent"]Employee
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]01/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]02/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]03/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]04/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]05/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]06/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]07/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]08/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]09/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]10/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]12/jan
[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13/jan
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Captain America
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]A
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]T
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]T
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]Incredible Hulk
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"] A
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Spider Man
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]A
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]Iron Man
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]T
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]****************
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]7
[/TD]
[TD="class: xl65, bgcolor: transparent"]****************
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"]Employee
[/TD]
[TD="class: xl65, bgcolor: transparent"]Days
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]9
[/TD]
[TD="class: xl65, bgcolor: transparent"]Captain America
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"]Incredible Hulk
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]11
[/TD]
[TD="class: xl65, bgcolor: transparent"]Spider Man
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]12
[/TD]
[TD="class: xl65, bgcolor: transparent"]Iron Man
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #D9D9D9"]****
[/TD]
[TD="class: xl65, bgcolor: transparent"]****************
[/TD]
[TD="class: xl69, bgcolor: transparent"]********
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[JoeMo]

This looks like a duplicate post to this one:
http://www.mrexcel.com/forum/excel-...employee-last-day-recorded-absence-tardy.html
Have you looked at the responses to this post?